SQL分析 – 豆包

2025-11-20 Created by Yuyang.Wang

提示词：

请分析这份SQL文件，YAML字段名已经给出（{ 字段名 }），按从最外层查询→所有中间层子查询→最内层数据表的完整层级，详细拆解每个字段的来源、处理过程、算法，以表格形式输出。表格需包含列：YAML字段名、最外层查询SQL字段（保持原始出现顺序和表达式，不简化、不合并、不省略，含空值并标注为null）、所有子查询字段（多列，每列列名为子查询别名,内容应当为SQL源码）、关联关系说明、最外层查询表（需标注字段最原始的基础表，而非子查询，方便运维查找）。要求保留完整原始SQL表达式，不修改任何内容，不合并相似字段，所有字段（含重复字段）全部呈现，用一个完整表格输出，不分块。附件xlsx为样例。

用法（以物联网APN维度为例）：

找到配置文件处的如下字段路径：gomp/dc-converge/standard/cron/iot/rpt/application-iot-rpt-dpi-kpi.yml

    iot_rpt_kpi_dpi_hour_apn: #核心侧指标：小时-apn
      enable: true
      cron: "0 35 * * * ? "
      path: standard/sql/iot/rpt/iot-rpt-dpi-kpi/merge/apn.sql     # 文件路径
      attrindex: 0   #
      rptCycle: "02"
      gatherMethod: clickhouse  # 从clickhouse执行sql
      tempTable:        # 中间使用的临时表， 需要清空数据的表
      processName: commonService          # 默认处理服务
      params: {"delayTime": "12", tab_cycle: "hour"}
      insertSql: "insert into iot_rpt_dpi_kpi_hour(id,import_time,create_time,rpt_time,rpt_start_time,rpt_end_time,rpt_cycle,
                            rpt_dimension,object_id,object_name,attach_success_rate,attach_delay,authentication_rate,authentication_delay,pdp_activation_rate,pdp_activation_delay,rau_change_rate,
                            2g_country_attach_rate,4g_country_attach_rate,province_user_num,incoming_user_num,terminal_online_num,active_user_num,2g_active_user_num,4g_active_user_num,nb_active_user_num,
                            up_down_flow,sign_terminal_num,active_terminal_num,inactive_terminal_num,terminal_online_rate,up_flow,down_flow,tcp_build_rate,http_request_rate,dns_request_rate,
                            nb_buss_rate,health_code_tcp_rate,health_code_tcp_delay,attach_suc_eps_num,attach_req_eps_num,active_suc_pdp_num,active_req_pdp_num,service_suc_num,service_req_num,
                            apn_access_success_rate,tcp_retrans_rate,up_tcp_retrans_rate,down_tcp_retrans_rate,tcp_outoforder_rate, apn_resp_duration,
                    exclude_user_cause_pdn_conn_succ_rate,paging_succ_rate,user_num,tcp_resp_delay,
                    tcp_up_avg_rate,tcp_down_avg_rate,rtt_up_avg_delay,rtt_down_avg_delay,
                    ue_pdn_succ_cnt,ue_pdn_req_cnt,rate_rely_succ,attach_succ_cnt,attach_req_cnt,network_type,server_ip,
                            tcp_req_cnt,tcp_succ_cnt,  tcp_up_flow_sum, tcp_up_dura, tcp_down_flow_sum, tcp_down_dura,
                            http_session_req_cnt,  http_session_response_succ_cnt,  tcp_packetu_sum,  tcp_resend_packetu_sum,
                            tcp_packetd_sum,  tcp_resend_packetd_sum,  paging_success_cnt,  paging_cnt,  dns_req_cnt,  dns_succ_cnt,include_user_cause_pdn_conn_succ_rate,
                            gbcall_succ_cnt,gbcall_req_cnt,
                    tcp_resp_delay_sum,tcp_syn_ack_avg_duration,tcp_syn_ack_duration,tcp_syn_ack_succ,tcp_ack_avg_duration,tcp_ack_duration,tcp_ack_succ,
                    ul_outoforder_pkts,dl_outoforder_pkts,http_resp_duration,rtt_up_avg_delay_sum,tcp_ul_total_rtt_time,
                    rtt_down_avg_delay_sum,tcp_dl_total_rtt_time,dns_res_duration,dns_res_avg_duration,tcp_up_flow
                  ) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,
                      ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
                 "
      insertDb: clickhouse  # 插入到clickhouse
      province: shanxi,neimeng,guangxi,beijing,hunan,liaoning
      vm: iot_rpt_dpi_kpi

提取出其中的字段名

id,import_time,create_time,rpt_time,rpt_start_time,rpt_end_time,rpt_cycle,
                            rpt_dimension,object_id,object_name,attach_success_rate,attach_delay,authentication_rate,authentication_delay,pdp_activation_rate,pdp_activation_delay,rau_change_rate,
                            2g_country_attach_rate,4g_country_attach_rate,province_user_num,incoming_user_num,terminal_online_num,active_user_num,2g_active_user_num,4g_active_user_num,nb_active_user_num,
                            up_down_flow,sign_terminal_num,active_terminal_num,inactive_terminal_num,terminal_online_rate,up_flow,down_flow,tcp_build_rate,http_request_rate,dns_request_rate,
                            nb_buss_rate,health_code_tcp_rate,health_code_tcp_delay,attach_suc_eps_num,attach_req_eps_num,active_suc_pdp_num,active_req_pdp_num,service_suc_num,service_req_num,
                            apn_access_success_rate,tcp_retrans_rate,up_tcp_retrans_rate,down_tcp_retrans_rate,tcp_outoforder_rate, apn_resp_duration,
                    exclude_user_cause_pdn_conn_succ_rate,paging_succ_rate,user_num,tcp_resp_delay,
                    tcp_up_avg_rate,tcp_down_avg_rate,rtt_up_avg_delay,rtt_down_avg_delay,
                    ue_pdn_succ_cnt,ue_pdn_req_cnt,rate_rely_succ,attach_succ_cnt,attach_req_cnt,network_type,server_ip,
                            tcp_req_cnt,tcp_succ_cnt,  tcp_up_flow_sum, tcp_up_dura, tcp_down_flow_sum, tcp_down_dura,
                            http_session_req_cnt,  http_session_response_succ_cnt,  tcp_packetu_sum,  tcp_resend_packetu_sum,
                            tcp_packetd_sum,  tcp_resend_packetd_sum,  paging_success_cnt,  paging_cnt,  dns_req_cnt,  dns_succ_cnt,include_user_cause_pdn_conn_succ_rate,
                            gbcall_succ_cnt,gbcall_req_cnt,
                    tcp_resp_delay_sum,tcp_syn_ack_avg_duration,tcp_syn_ack_duration,tcp_syn_ack_succ,tcp_ack_avg_duration,tcp_ack_duration,tcp_ack_succ,
                    ul_outoforder_pkts,dl_outoforder_pkts,http_resp_duration,rtt_up_avg_delay_sum,tcp_ul_total_rtt_time,
                    rtt_down_avg_delay_sum,tcp_dl_total_rtt_time,dns_res_duration,dns_res_avg_duration,tcp_up_flow

使用删除回车换行工具去除换行例如在线删除回车：在线删除回车换行工具 - 一键合并多行文本—LZL在线工具

id,import_time,create_time,rpt_time,rpt_start_time,rpt_end_time,rpt_cycle,rpt_dimension,object_id,object_name,attach_success_rate,attach_delay,authentication_rate,authentication_delay,pdp_activation_rate,pdp_activation_delay,rau_change_rate,2g_country_attach_rate,4g_country_attach_rate,province_user_num,incoming_user_num,terminal_online_num,active_user_num,2g_active_user_num,4g_active_user_num,nb_active_user_num,up_down_flow,sign_terminal_num,active_terminal_num,inactive_terminal_num,terminal_online_rate,up_flow,down_flow,tcp_build_rate,http_request_rate,dns_request_rate,nb_buss_rate,health_code_tcp_rate,health_code_tcp_delay,attach_suc_eps_num,attach_req_eps_num,active_suc_pdp_num,active_req_pdp_num,service_suc_num,service_req_num,apn_access_success_rate,tcp_retrans_rate,up_tcp_retrans_rate,down_tcp_retrans_rate,tcp_outoforder_rate, apn_resp_duration,exclude_user_cause_pdn_conn_succ_rate,paging_succ_rate,user_num,tcp_resp_delay,tcp_up_avg_rate,tcp_down_avg_rate,rtt_up_avg_delay,rtt_down_avg_delay,ue_pdn_succ_cnt,ue_pdn_req_cnt,rate_rely_succ,attach_succ_cnt,attach_req_cnt,network_type,server_ip,tcp_req_cnt,tcp_succ_cnt,  tcp_up_flow_sum, tcp_up_dura, tcp_down_flow_sum, tcp_down_dura,http_session_req_cnt,  http_session_response_succ_cnt,  tcp_packetu_sum,  tcp_resend_packetu_sum,tcp_packetd_sum,  tcp_resend_packetd_sum,  paging_success_cnt,  paging_cnt,  dns_req_cnt,  dns_succ_cnt,include_user_cause_pdn_conn_succ_rate,gbcall_succ_cnt,gbcall_req_cnt,tcp_resp_delay_sum,tcp_syn_ack_avg_duration,tcp_syn_ack_duration,tcp_syn_ack_succ,tcp_ack_avg_duration,tcp_ack_duration,tcp_ack_succ,ul_outoforder_pkts,dl_outoforder_pkts,http_resp_duration,rtt_up_avg_delay_sum,tcp_ul_total_rtt_time,rtt_down_avg_delay_sum,tcp_dl_total_rtt_time,dns_res_duration,dns_res_avg_duration,tcp_up_flow

将要分析的SQL文件添加 .txt 后缀名例如：apn.sql -> apn.sql.txt

豆包会限制上传一些有敏感操作的SQL文件

将处理好的字段名替换原提示词中的 { 字段名 }

请分析这份SQL文件，YAML字段名已经给出（id,import_time,create_time,rpt_time,rpt_start_time,rpt_end_time,rpt_cycle,rpt_dimension,object_id,object_name,attach_success_rate,attach_delay,authentication_rate,authentication_delay,pdp_activation_rate,pdp_activation_delay,rau_change_rate,2g_country_attach_rate,4g_country_attach_rate,province_user_num,incoming_user_num,terminal_online_num,active_user_num,2g_active_user_num,4g_active_user_num,nb_active_user_num,up_down_flow,sign_terminal_num,active_terminal_num,inactive_terminal_num,terminal_online_rate,up_flow,down_flow,tcp_build_rate,http_request_rate,dns_request_rate,nb_buss_rate,health_code_tcp_rate,health_code_tcp_delay,attach_suc_eps_num,attach_req_eps_num,active_suc_pdp_num,active_req_pdp_num,service_suc_num,service_req_num,apn_access_success_rate,tcp_retrans_rate,up_tcp_retrans_rate,down_tcp_retrans_rate,tcp_outoforder_rate, apn_resp_duration,exclude_user_cause_pdn_conn_succ_rate,paging_succ_rate,user_num,tcp_resp_delay,tcp_up_avg_rate,tcp_down_avg_rate,rtt_up_avg_delay,rtt_down_avg_delay,ue_pdn_succ_cnt,ue_pdn_req_cnt,rate_rely_succ,attach_succ_cnt,attach_req_cnt,network_type,server_ip,tcp_req_cnt,tcp_succ_cnt,  tcp_up_flow_sum, tcp_up_dura, tcp_down_flow_sum, tcp_down_dura,http_session_req_cnt,  http_session_response_succ_cnt,  tcp_packetu_sum,  tcp_resend_packetu_sum,tcp_packetd_sum,  tcp_resend_packetd_sum,  paging_success_cnt,  paging_cnt,  dns_req_cnt,  dns_succ_cnt,include_user_cause_pdn_conn_succ_rate,gbcall_succ_cnt,gbcall_req_cnt,tcp_resp_delay_sum,tcp_syn_ack_avg_duration,tcp_syn_ack_duration,tcp_syn_ack_succ,tcp_ack_avg_duration,tcp_ack_duration,tcp_ack_succ,ul_outoforder_pkts,dl_outoforder_pkts,http_resp_duration,rtt_up_avg_delay_sum,tcp_ul_total_rtt_time,rtt_down_avg_delay_sum,tcp_dl_total_rtt_time,dns_res_duration,dns_res_avg_duration,tcp_up_flow），按从最外层查询→所有中间层子查询→最内层数据表的完整层级，详细拆解每个字段的来源、处理过程、算法，以表格形式输出。表格需包含列：YAML字段名、最外层查询SQL字段（保持原始出现顺序和表达式，不简化、不合并、不省略，含空值并标注为null）、所有子查询字段（多列，每列列名为子查询别名,内容应当为SQL源码）、关联关系说明、最外层查询表（需标注字段最原始的基础表，而非子查询，方便运维查找）。要求保留完整原始SQL表达式，不修改任何内容，不合并相似字段，所有字段（含重复字段）全部呈现，用一个完整表格输出，不分块。附件xlsx为样例。

将处理好的提示词连同 .sql.txt 文件、样例.xlsx 文件一同提交给豆包。样例.xlsx 文件获取方式：
FileCodeBox下载：https://file.sakurain.net/#/?code=E7CXS
Cloudrever下载：https://pan.sakurain.net/s/g7cG/cue1bs67