【pgSql 海量数据库操作记录】

一、批量插入数据【测试用】1.1、sql 语法sql 模板DO$$DECLAREiinteger:1;BEGINWHILEi10LOOPINSERTINTOmy_table(col1,col2,col3)VALUES(value1,value2,i);i :i1;ENDLOOP;END$$;案例测试 sqlDO$$DECLAREiinteger:1;BEGINWHILEi100LOOPINSERTINTOcampaign.mc_answer_record(answer_record_id,answer_user_id,answer_user_name,answer_user_mobile,theme_id,theme,answer_category,pass,delete_status,create_user,create_time,update_user,update_time,theme_category_id,score,time_consuming,used_share_times,type,game_type)VALUES(mc_answer_record_seq.nextVal,i1,汤玉祥,18867087968,10630,第51期答题活动,春节民俗,0,0,5001,2023-10-25 19:49:59,NULL,NULL,3902,0,i1,NULL,NULL,NULL);i :i1;ENDLOOP;END$$;二、更改数据库字段类型2.1、sql 语法altertabletable_namealtercolumncolumn_nametype类型;例如altertableintermediatetablealtercolumnphonetypevarchar(100);2.2、新增表字段ALTERTABLEyour_tableADDCOLUMNnew_column datatype;ALTERTABLEyour_tableADDCOLUMNnew_column datatypeDEFAULTdefault_value;-- 新增字段并设置默认值COMMENTONCOLUMNyour_table.your_columnISThis is a comment for the column;-- 字段添加注释-- 示例altertablemc_answer_recordaddcolumnreceive_statuschar(1);COMMENToncolumnmc_answer_record.receive_statusis领取奖品状态0.待领取1.已领取2.3、查询 information_schema.columns 视图来获取表字段的注释信息-- 查询 information_schema.columns 视图来获取表字段的注释信息SELECTcolumn_name,column_commentFROMinformation_schema.columnsWHEREtable_nameyour_table;三、添加索引3.1、单个字段索引CREATEINDEXidx_your_columnONyour_table(your_column);-- 单个字段索引在这个示例中idx_your_column 是索引的名称your_table 是表的名称your_column 是要创建索引的字段。 你可以根据自己的需求选择不同的索引类型。PostgreSQL 支持多种类型的索引包括 B-tree、哈希、GiST、SP-GiST、GIN 和 BRIN 等。3.2、复合索引CREATEINDEXidx_your_columnsONyour_table(column1,column2,...);-- 复合索引四、查询排行版4.1、查询前 100 个用户的排行版SELECTanswer_user_id custNum,answer_user_name custName,answer_user_mobile phone,nvl(score,0)score,nvl(time_consuming,0)timeConsuming,DENSE_RANK()OVER(ORDERBYscoreDESCNULLSLAST,time_consuming NULLSLAST)ASrankFROM(SELECTanswer_user_id,answer_user_name,answer_user_mobile,score,time_consuming,ROW_NUMBER()OVER(PARTITIONBYanswer_user_idORDERBYscoreDESCNULLSLAST,time_consumingASC)ASrnFROMmc_answer_recordWHEREtheme_id#{campaignId,jdbcTypeNUMERIC}) TWHERErn1limit#{num, jdbcTypeNUMERIC}4.2、查询自己的排行名次SELECTanswer_user_id custNum,score score,time_consuming timeConsuming,answer_user_name custName,answer_user_mobile phone,rankFROM(SELECTanswer_user_id,score,time_consuming,answer_user_name,answer_user_mobile,DENSE_RANK()OVER(ORDERBYscoreDESCNULLSLAST,time_consuming NULLSLAST)ASrankFROMmc_answer_recordWHEREtheme_id#{campaignId,jdbcTypeNUMERIC})WHEREanswer_user_id#{userId,jdbcTypeNUMERIC}ANDrownum14.3、查询排名前 100 的用户SELECTcustNum,custName,phone,score,timeConsuming,rank from(SELECTanswer_user_id custNum,answer_user_name custName,answer_user_mobile phone,nvl(score,0)score,nvl(time_consuming,0)timeConsuming,DENSE_RANK()OVER(ORDERBYscoreDESCNULLSLAST,time_consumingNULLSLAST)ASrankFROM(SELECTanswer_user_id,answer_user_name,answer_user_mobile,score,time_consuming,ROW_NUMBER()OVER(PARTITIONBYanswer_user_idORDERBYscoreDESCNULLSLAST,time_consumingASC)ASrnFROMmc_answer_recordWHEREtheme_id#{campaignId,jdbcTypeNUMERIC})TWHERErn1ORDERBYscoreDESCNULLSLAST,time_consumingASC)where rank![CDATA[]]#{num,jdbcTypeNUMERIC}五、查询时间周期5.1、查询本周一 本周日的时间区间SELECTTRUNC(NEXT_DAY(sysdate-8,1)1),TRUNC(NEXT_DAY(sysdate-8,1)7)FROMmc_campaign;5.2、查询当天本周本月开始时间 结束时间但是在 sql 中进行了函数操作会导致索引失效建议直接放到代码中处理时间然后 sql 直接拼接处理好些choosewhen testdateType day--本日参与成功的数据SELECTCOUNT(1)FROMMC_CAMPAIGN_JION_RECORDaWHERETO_CHAR(a.JION_TIME,YYYY-MM-DD)TO_CHAR(now(),YYYY-MM-DD)andCAMPAIGN_ID#{campaignId,jdbcTypeNUMERIC}andCUST_NUM#{userId,jdbcTypeNUMERIC}andJION_STUTSin(1,2)andHOLD_TIMES1/whenwhen testdateType week--本周参与成功的数据SELECTCOUNT(1)FROMMC_CAMPAIGN_JION_RECORDWHEREJION_TIMEgt;TRUNC(NEXT_DAY(sysdate-8,1)1)ANDJION_TIMElt;TRUNC(NEXT_DAY(sysdate-8,1)7)1andCAMPAIGN_ID#{campaignId,jdbcTypeNUMERIC}andCUST_NUM#{userId,jdbcTypeNUMERIC}andJION_STUTSin(1,2)andHOLD_TIMES1/whenwhen testdateType month--本月参与成功的数据SELECTCOUNT(1)FROMMC_CAMPAIGN_JION_RECORDWHERETO_CHAR(JION_TIME,YYYY-MM)TO_CHAR(now(),YYYY-MM)andCAMPAIGN_ID#{campaignId,jdbcTypeNUMERIC}andCUST_NUM#{userId,jdbcTypeNUMERIC}andJION_STUTSin(1,2)andHOLD_TIMES1/whenotherwise--默认全部参与成功的数据SELECTCOUNT(1)FROMMC_CAMPAIGN_JION_RECORDWHERECAMPAIGN_ID#{campaignId,jdbcTypeNUMERIC}andCUST_NUM#{userId,jdbcTypeNUMERIC}andJION_STUTSin(1,2)andHOLD_TIMES1/otherwise/choose六、函数操作6.1、两张表关联字符串ids 关联 数字型id举例文章表存放的是分类id字符串【2022,2023,2024】这种一篇文章对应多个分类关联文章分类表分类ID-- 每次看执行计划养成良好习惯并且先在生产上执行一下看看速度explainSELECTA.ID,array_to_string(ARRAY_AGG(AC.CATEGORY_NAME),,)AScategoryName,A.ARTICLE_TITLEASarticleTitle,A.CREATE_TIMEAScreateTime,nvl(A.like_num,0)likeNum,nvl(A.collect_num,0)collectNum,nvl(A.read_num,0)readNum,nvl(A.comment_num,0)commentNum,(SELECTCOUNT(1)FROMcampaign.mc_share_record MWHEREA.IDM.busi_idANDM.busi_type1)shareNumFROMcampaign.mc_article ALEFTJOINcampaign.mc_article_category ACONAC.ARTICLE_CATEGORY_IDANY(string_to_array(regexp_replace(A.ARTICLE_CATEGORY_ID,[^\d], ,g), )::INT[])GROUPBYA.IDORDERBYA.CREATE_TIMEDESCNULLSLAST;执行效果图七、查询表字段注释为空脚本selectdistinctg.schemaname 用户名,c.relname 表名,cast(obj_description(relfilenode,pg_class)asvarchar)名称,a.attname 字段,d.description 字段备注,concat_ws(,t.typname,SUBSTRING(format_type(a.atttypid,a.atttypmod)from))as列类型frompg_class cleftjoinpg_attribute aona.attrelidc.oidleftjoinpg_type tona.atttypidt.oidleftjoinpg_description dond.objoida.attrelidandd.objsubida.attnumleftjoinpg_tables gonupper(g.tablename)upper(c.relname)wherea.attnum0andg.schemanamein(campaign,glmall,imauth,mallapp,mallcollect,mallgoodsdb,mallinf,mallmerchantdb,mallorderdb,mallreportdb,workflowdb)andd.descriptionisnulland(c.relnamenotlike%bak%andc.relnamenotlike%0%andc.relnamenotlike%1%andc.relnamenotlike%2%andc.relnamenotlike%3%andc.relnamenotlike%4%andc.relnamenotlike%5%andc.relnamenotlike%6%andc.relnamenotlike%7%andc.relnamenotlike%8%andc.relnamenotlike%9%andc.relnamenotlikeold_%)orderbyg.schemaname,c.relname;八、分类ids关联分类表搂出分类名称SELECTa.category_ids,array_to_string(array_agg(distinctb.category_name),,)ASchinese_namesFROMpms_goods_base_info aleftJOINpms_goods_category bONb.goods_category_idANY(string_to_array(a.category_ids,,)::int[])GROUPBYa.category_ids;效果图