hongxing 数据开发常用脚本

【将json字符串转成多列的函数】

WITH
      cte_base_i18n_lang_dict AS (SELECT explode(udf.read_json(zh_cn)) AS j_zh_cn
                                FROM stg.stg_base_i18n_lang_dict
                                WHERE dt = '2025-10-12'
                                  AND delete_flag = '0'
                                  AND code in ('enum_inventory_BillTypeInEnum', 'enum_inventory_BillTypeOutEnum',
                                               'enum_AdjustBillTypeEnum')
                                  AND type = '2'
                                  AND state = '0'),
    -- 处理json
    cte_i18 AS (SELECT get_json_object(j_zh_cn, '$.code') AS code, get_json_object(j_zh_cn, '$.desc') AS desc
                FROM cte_base_i18n_lang_dict)
SELECT * FROM cte_i18

【IDB审核平台】导入表建表

create table ld_mapping_fixed_price_city_cost
(
    id                           bigint unsigned not null primary key auto_increment comment '数据表主键',

    upload_batch_no              varchar(255)    not null default '' comment '上传操作-批次号',
    delete_flag                  int             not null default 0 comment '删除 默认；0:不删除,1:已删除',
    create_user                  varchar(20)     not null default '' comment '创建人',
    create_user_name             varchar(30)     not null default '' comment '创建人姓名',
    create_time                  datetime        not null default CURRENT_TIMESTAMP comment '创建时间',
    edit_user                    varchar(20)     not null default '' comment '修改人',
    edit_user_name               varchar(30)     not null default '' comment '修改人姓名',
    edit_time                    datetime        not null default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP comment '修改时间'
) comment '一口价竞价城市成本参数表'

alter table ld_asset_operations_vietnam_offline_import
  add column unit varchar(400) NOT NULL DEFAULT '' COMMENT '单位' after ratio

【快捷键】

• 修改电脑开机密码:Ctrl+Alt+Delete
• 无痕模式；Ctrl+Shift+N
• 标记多列：ctrl+Alt+向下箭头

【finebi基本使用】

编辑链接

预览链接 –仪表盘预览链接;http://ip:端口/webroot/decision/v5/design/report/此处放置仪表板ID/view

【数据中台】

【中台任务-shell】

ssh root@prd-za-hadoop01.hd "su hdfs -s /data/command/core_shell/hdfs_to_doris.sh ads ads_store_health_dashboard nopart all 2025-02-24 2025-02-24 fanruan fanruan_hive_ads_store_health_dashboard prd_report_doris"
ssh root@prd-za-hadoop01.hd "su hdfs -s /data/command/core_shell/hdfs_to_doris.sh rpt rpt_material_warehouse_day  dt inc $1 $2 fanruan fanruan_hive_rpt_material_warehouse_day prd_report_doris"

【中台调参数】

//任务执行效率低调高 park.num.executors、driver.memory
spark.name：rpt_collection_letter_registration
spark.executor.memory：12
spark.executor.cores：4
spark.num.executors：4
spark.sql.shuffle.partitions：32
driver.memory：2
spark.executor.memory：spark.executor.cores = 3:1
spark.sql.shuffle.partitions = spark.executor.cores * spark.num.executors * 2

//业务代码中必须用到笛卡尔集时
set spark.sql.crossJoin.enabled=true;

【中台建表】

--建表语句
create external table dwd|dws|ads|rpt_test_table (
column_name type comment '测试',
...
) COMMENT '测试'
-- 分区表 partitioned by (dt string comment '按天分区') 
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\u0001' 
STORED AS PARQUET 
TBLPROPERTIES (
'transactional' = 'false',
'parquet.compression' = 'snappy'
);

【测试表恢复】

INSERT OVERWRITE TABLE rpt_dev.rpt_business_managers_lease_change_data
        select null,null,null,null,null,null,null,null,null,null,null,null

【doris操作】

【 doris增减分区脚本】

--分区表参考：设备信息表
ALTER TABLE fanruan_hive_ads_fin_comn_store_repair_fee_da SET ("dynamic_partition.enable" = "true")
ALTER TABLE fanruan_hive_ads_fin_comn_store_repair_fee_da DROP PARTITION p20250228;
ALTER TABLE fanruan_hive_ads_fin_comn_store_repair_fee_da ADD PARTITION p20250129 VALUES [('2025-01-29'), ('2025-01-30'))

--表字段前加字段
ALTER TABLE ld_hr_solution_coef ADD COLUMN advice_soln varchar(500) not null default '' COMMENT '提点方案' AFTER price_coef_start_date

--doris添加列
alter table fanruan_hive_rpt_material_warehouse_day add column mat_property string comment '材料产权';

--增加索引
ALTER TABLE `ads_spare_ex_prog_track_local` 
  ADD KEY `idx_account_code_status_time` (`account_set_code`, `original_code`, `demand_status`, `demand_create_time`);

【经典案列】

• 列转行：项目维度应收应付结算付款汇总表
• 数据测试

select count(1) from stg.stg_fac_voucher_detail where dt = '${bizdate,yyyy-MM-dd,day,-,0}'
and delete_flag = '0'
union ALL
select count(distinct voucher_id,row_id) from stg.stg_fac_voucher_detail where dt = '${bizdate,yyyy-MM-dd,day,-,0}'
and delete_flag = '0'

select voucher_id,count(1) as tp from stg.stg_fac_voucher_header where dt = '${bizdate,yyyy-MM-dd,day,-,0}'
and delete_flag = '0'
group by voucher_id having count(1)>1
order by tp

select * from stg.stg_fac_voucher_header where dt = '${bizdate,yyyy-MM-dd,day,-,0}'
and delete_flag = '0' and voucher_id = '571525492425105408'

⚙️ 工作台 · 常用函数库

主键	函数 / 表达式	说明	日期

开发易错点整理

类型	关键字	问题描述	记录时间	备注	补充说明
Spark	分号	join语句失效，是因为join上面语句注释有分号	2025-03-19	注释一定不要有特殊符号	无
Spark	application作为字段名	测试能跑通，发布就报错	2025-03-19	不要用关键字作为字段名字，实在要用加反引号	无
Spark	join	join的关联字段一定要类型一致	2025-03-19	join 字母 a=数字97 会关联上	无
Spark	join	join的关联字段不能有null	2025-03-19	会产生笛卡尔积	关联条件两边都是null导致条件失效产生笛卡尔积
中台	补数	众安平台补数不会跳过自定义crontab	2025-03-21	crontab依赖上游表，补数会忽略执行条件，统一设最晚执行时间	无
Spark	建表	doris表的源头表应该parquet格式	未记录	doris抽取任务拉取parquet文件，orc格式会抽取失败	无
Spark	测试环境，测试报分区相关错误	测试环境分区表目录缺失分区层级，执行报错	2025-07-18	删除分区+修复表元数据：ALTER TABLE … DROP IF EXISTS PARTITION(…); msck repair table …;	无
Spark	比较(> < max min 等）	数字和字符类型比较大小规则不一致，需类型转换	未记录	‘9’>’12’，9<12	无
Spark	分区每月只跑一天	补数会刷新分区数据，需加逻辑避免	2025-07-31	substr(if(dayofmonth(current_date) <> 5, current_date, date_sub(trunc(‘${bizdate}’,’MONTH’),1)),1,7)	无
Spark	udf	能用原生语法实现尽量不用UDF	2025-08-07	UDF只用来实现原生无法实现的逻辑，提升迁移可移植性	反馈问题记录
Spark	判断空值	ad<> null 会跑出空数据	2025-08-07	正确写法：ad is not null	反馈问题记录
Spark	无关联条件连接	两张表无关联条件直接连接会报错	2025-08-07	必须用笛卡尔集时：set spark.sql.crossJoin.enabled=true;	反馈问题记录
Spark	测试和生产数据对不上	迭代需求数据量不一致	2025-09-12	检查代码中是否带入${xxx_project}等参数不一致	无
Spark	产生不干净分区	偶发不干净分区，报路径不存在错误	2025-10-22	删除异常分区+修复元数据，建议重跑数据	无
中台	日期	线下导入日期为字符串时注意格式	2025-11-14	统一转成yyyy-MM-dd，如2025/1/1→2025-01-01	无
中台	特殊符号	调度配置含< >等特殊字符需转义	2025-11-25	例：时点利润<0 → 时点利润%3C0	无
中台	in not in	hive中in+not in≠总数，因包含null	2025-12-10	与产品确认是否需包含null数据	无
Spark	row_id	with语法多次调用导致row_number结果不一致	2026-01-08	order by 数据不唯一导致多次计算结果不一致	无
帆软	concat	Doris拼接字段用		会类型识别错误	2026-03-13

报表管理

• 投标管理看板 联系 运营 gaokangji01

  INSERT OVERWRITE TABLE  ${rpt_project}.rpt_tender_management_dashboard
  SELECT
   YEAR(a.create_time) AS year,
   MONTH(a.create_time) AS month,
   a.shop_code,
   a.shop_name,
   -- 累计投标数：投标阶段=投标或空，按单据编号去重计数
   COUNT(DISTINCT CASE WHEN a.tender_stage = '2' OR a.tender_stage = '0' THEN b.flow_no END) AS tender_count,
   -- 已出结果数：投标结果=未中标或已中标，按单据编号去重计数
   COUNT(DISTINCT CASE WHEN d.progress IN ('2', '3', '4', '5') THEN b.flow_no END) AS result_count,
   -- 有效投标数：投标结果=未中标或已中标 且 丢单原因!=客户原因，按单据编号去重计数
   COUNT(DISTINCT CASE WHEN d.progress IN ('2', '3', '4', '5') AND (d.loss_order_reason NOT IN ('25', '26', '27', '28')) THEN b.flow_no END) AS valid_tender_count,
   -- 投标金额(万)：投标阶段=投标或空，投标金额汇总/10000
   coalesce(SUM(CASE WHEN a.tender_stage = '2' OR a.tender_stage = '0' THEN a.tender_amount ELSE 0 END), 0) / 10000 AS tender_amount_wan,
   -- 中标项目数：投标结果=已中标，按单据编号去重计数
   COUNT(DISTINCT CASE WHEN d.progress IN ('3', '4', '5') THEN b.flow_no END) AS win_count,
   -- 中标金额(万)：投标结果=已中标，中标金额为0取投标金额，汇总/10000
          coalesce(SUM(CASE WHEN d.progress IN ('3', '4', '5') THEN CASE WHEN d.bid_winning_amount = 0 THEN a.tender_amount ELSE d.bid_winning_amount END ELSE 0 END), 0) / 10000 AS win_amount_wan,
   -- 中标率：中标项目数/有效投标数
   ROUND(
   coalesce(
   COUNT(DISTINCT CASE WHEN d.progress IN ('3', '4', '5') THEN b.flow_no END)
   / NULLIF(COUNT(DISTINCT CASE WHEN d.progress IN ('2', '3', '4', '5') AND (d.loss_order_reason NOT IN ('25', '26', '27', '28')) THEN b.flow_no END), 0)
   , 0)
   , 4) AS win_rate
   ,store.wararea_name
   ,store.dept_name
  FROM stg.stg_tender_flow b
  LEFT JOIN stg.stg_tender_apply a ON b.flow_no = a.flow_no
  LEFT JOIN stg.stg_tender_project d ON b.flow_no = d.flow_no
  left join dwd.dim_store store 
      on store.store_cd = a.shop_code and store.dt = '${dt}'
  WHERE b.delete_flag = '0' and b.dt = '${dt}'
   AND a.delete_flag = '0' and a.dt = '${dt}'
   AND d.delete_flag = '0' and d.dt = '${dt}'
   AND b.status = '3'
  GROUP BY YEAR(a.create_time), MONTH(a.create_time), a.shop_code, a.shop_name,store.wararea_name,store.dept_name
  ORDER BY year DESC, month DESC

• 设备异常追索管理报表 联系 供应链 刘玉宝

  SELECT
      h.exception_no                                    -- 异常闭环单单号,
      , CASE h.exception_status WHEN '0' THEN '草稿' WHEN '1' THEN '审批中' WHEN '2' THEN '已审批' WHEN '3' THEN '已作废' WHEN '4' THEN '已完成' ELSE '' END AS exception_status_desc                  -- 异常闭环单状态
      , coalesce(h.create_by_name,'') || '&&' ||coalesce(h.create_by,'') as  create_by                                   -- 创建人
      , h.create_time                                   -- 创建时间
      , store.wararea_name                              -- 区域
      , h.org_dept_name                                 -- 门店
      , coalesce(store.store_mgr_name,'') || '&&' ||coalesce(store.store_mgr_ad_no,'') as store_mgr   --营业店负责人
      , h.net_name  --网点/项目信息
      , equ.item_name  --设备名称
      , h.equ_no                                        -- 设备编号
      , h.machine_code                                  -- 主机编号
      , equ.equ_position_state_cd --位置状态
      , equ.asset_prop_type --设备性质
      , asset.gp_net_val_amt --财务净值
      , h.create_time          as exception_time                         -- 异常发生时间
      , CASE h.responsible_entity WHEN '1' THEN '我司原因' WHEN '2' THEN '客户原因' ELSE '' END AS responsible_entity_desc               -- 责任方
      , h.ro_no                                         -- 租赁订单编号
      , h.customer_name                                 -- 客户名称
      , CASE h.customer_qualification WHEN '1' THEN 'A类' WHEN '2' THEN 'B类' WHEN '3' THEN 'C类' WHEN '4' THEN 'D类' WHEN '5' THEN 'E类' ELSE '' END AS customer_qualification_desc           -- 客户等级分类
      , CASE h.exception_type WHEN '1' THEN '丢失' WHEN '2' THEN '全损' WHEN '3' THEN '被扣押' WHEN '4' THEN '私自转场' ELSE '' END AS exception_type_desc                   -- 异常类型
      , h.exception_desc                                -- 异常说明
      , CASE h.equ_search_result WHEN '0' THEN '未知' WHEN '1' THEN '异常解决(申请闭环)' WHEN '2' THEN '向客户追索(下推灭失定损)' ELSE '' END AS equ_search_result_desc                -- 确认寻车结果
      , h.old_part_recycle_push_flag                    -- 残体情况(原始值)
      --, sourcetype                                    -- 来源单据类型
      --, sourceno                                      -- 来源单据编号
  from stg.stg_so_equ_exception h
  left join dwd.dim_store store 
      on store.store_cd = h.org_dept_code and store.dt = '2026-07-23'
  left join dwd.fct_equ_card_dtl equ 
      on equ.equ_no = h.equ_no 
  left join rpt.rpt_fin_asset_card asset
      on asset.saas_asset_no = h.equ_no 
  where h.delete_flag = '0' and h.dt = '2026-07-23'