AI在实际工作中的应用案例分享

以下是利用QClaw进行fssc etl报表迁移项目的开发案例：
  
```
{
“job”: {
“setting”: {
“speed”: {
“channel”: 1
},
“errorLimit”: {
“record”: 0
}
},
“content”: [
{
“reader”: {
“name”: “mysqlreader”,
“parameter”: {
“username”: “normal.FSSC.jdbc.username","password":"
normal.FSSC.jdbc.username","password":"{norm…ord}”,
“connection”: [{
“jdbcUrl”: [“${normal.FSSC.jdbc.url}”],
“querySql”: ["SELECT ebr.reim_code,
ebr.cust_order_type,
ftl.bill_id,#单据号
ftl.bill_type_id,#单据类型id
ftbt.cust_bill_type_code,#单据编号
ftbt.cust_bill_type_name,#单据类型名称
ftl.log_start_time,#开始时间
ftl.log_end_time,#结束时间

ftl.waited_time/60 as waited_time, #等待时间
MyDateSubWorkTime (
TIMESTAMP ( ftl.log_start_time ),
TIMESTAMP ( ftl.log_end_time ))/ 60 AS waited_time,
ebr.apply_by,#申请人id
ebr.apply_name,#申请人名称
ebr.apply_center_code,#申请人成本中心编码
ebr.apply_center_name,#申请人成本中心名称
ebr.apply_company_name,#申请人公司名称
ebr.apply_company_code,#申请人公司编码
ebr.apply_company_id,#申请人公司id
ebr.company_name,#入账公司名称
ebr.company_code,#入账公司编码
ebr.company_id,#入账公司id
#if(ebr.company_code = ‘1600’,‘圣迪乐村’,temp.plate_name) as plate_nam,
#if(ebr.company_code = ‘1600’,‘00004000’,temp.plate_code) as plate_code,
#if(ebr.company_code = ‘1600’,‘5’,temp.plate_id) as plate_id,
temp.plate_name,#入账公司事业部id
temp.plate_code,#入账公司事业部id
temp.plate_id,#入账公司事业部id
ftl.operator_id,#稽核岗人员id
ftl.operator_name,#稽核岗人员名称
ftl.operator_group_id,#稽核岗组别id
fttg.task_group_name,#组别名称
fttg.pool_type_code,#池类型编号
YEAR ( ftl.log_end_time ) AS year_id,
YEAR ( ftl.log_end_time ) AS year_code,
CONCAT( YEAR ( ftl.log_end_time ), ‘年’ ) year_name,
MONTH ( ftl.log_end_time ) AS month_id,
ebr.order_status AS month_code,#修改为审批状态
CONCAT( MONTH ( ftl.log_end_time ), ‘月’ ) month_name,
DAY ( ftl.log_end_time ) AS day_id,
DAY ( ftl.log_end_time ) AS day_code,
CONCAT( DAY ( ftl.log_end_time ), ‘日’ ) AS day_name,
DATE_FORMAT( ftl.log_end_time, ‘%Y-%m-%d’ ) AS date_name,#日期
ebr.order_status,#审批状态
CASE

WHEN ftbt.cust_bill_type_code IN ( ‘CA_MANUAL’, ‘CA_ACCOUNT_APPLY’ ) THEN
‘zongzhang’
WHEN ftbt.cust_bill_type_code IN ( ‘PA_ZX_YF’, ‘PA_ZX_YFYF’ ) THEN
‘yingfu’
WHEN ftbt.cust_bill_type_code IN ( ‘RF_KP’, ‘CA_JDX’, ‘CA_JDXPD’, ‘CL_CX’ ) THEN
‘yingshou’ ELSE ‘feiyong’
END AS CurrentGroupId0,
CASE

WHEN ftbt.cust_bill_type_code IN ( ‘CA_MANUAL’, ‘CA_ACCOUNT_APPLY’ ) THEN
‘总账工单组’
WHEN ftbt.cust_bill_type_code IN ( ‘PA_ZX_YF’, ‘PA_ZX_YFYF’) THEN
‘应付审核组’
WHEN ftbt.cust_bill_type_code IN ( ‘RF_KP’, ‘CA_JDX’, ‘CA_JDXPD’, ‘CL_CX’) THEN
‘应收审核组’ ELSE ‘费用审核组’
END AS task_Group_Name0,
be.employee_code #组别

FROM
fsop_tm_log ftl
INNER JOIN fsop_tm_task_pool fttp ON fttp.bill_id = ftl.bill_id
AND fttp.pool_type_code = ‘BILL_POOL_TYPE’
INNER JOIN fsop_tm_bill_type ftbt ON ftbt.bill_type_id = ftl.bill_type_id
INNER JOIN fsop_tm_task_group fttg ON fttg.task_group_id = ftl.operator_group_id
INNER JOIN exp_base_reim ebr ON ebr.reim_code = ftl.bill_code
LEFT JOIN ( SELECT * FROM bd_cfg_order_type WHERE order_class = ‘CUST_ORDER_TYPE’ ) bcot ON bcot.order_type_code = ebr.cust_order_type
LEFT JOIN bd_company temp ON temp.company_code = ebr.company_code
LEFT JOIN bd_employee be ON be.employee_id = ftl.operator_id
WHERE
ftl.current_node_code = ‘NODE_ACCOUNTING_AUDIT’
AND ftl.task_operate_type = ‘APPROVE’
AND ftl.operator_id != - 1
AND (
ftbt.cust_bill_type_code = ‘EC_CL’
OR ftbt.cust_bill_type_code = ‘EC_TY’
OR ftbt.cust_bill_type_code = ‘EC_SPECIAL’
OR ftbt.cust_bill_type_code = ‘EC_SL’
OR ftbt.cust_bill_type_code = ‘PA_LOAN’
OR ftbt.cust_bill_type_code = ‘CL_RP’
OR ftbt.cust_bill_type_code = ‘PA_PREPAY’
OR ftbt.cust_bill_type_code = ‘EC_EB_MC’
OR ftbt.cust_bill_type_code = ‘EC_EB_FC’
OR ftbt.cust_bill_type_code = ‘EC_CCS’
OR ftbt.cust_bill_type_code = ‘EC_SB’
OR ftbt.cust_bill_type_code = ‘PA_ZX_YF’
OR ftbt.cust_bill_type_code = ‘PA_ZX_YFYF’
OR ftbt.cust_bill_type_code = ‘CA_YT’
OR ftbt.cust_bill_type_code = ‘PA_ZX_CUR’
OR ftbt.cust_bill_type_code = ‘EC_MARKETING’
OR ftbt.cust_bill_type_code = ‘CA_MANUAL’
OR ftbt.cust_bill_type_code = ‘CA_ACCOUNT_APPLY’
OR ftbt.cust_bill_type_code = ‘RF_KP’
OR ftbt.cust_bill_type_code = ‘CA_JDX’
OR ftbt.cust_bill_type_code = ‘CA_JDXPD’
OR ftbt.cust_bill_type_code = ‘EC_TMS’
)
UNION
SELECT
ers.claim_order_code AS reim_code,
‘SKC’ AS cust_order_type,
ers.receipt_serial_id AS bill_id,
‘’ AS bill_type_id,
‘SKC’ AS cust_bill_type_code,
‘收款池’ AS cust_bill_type_name,
ers.claim_date AS log_start_time,
ers.claim_date AS log_end_time,
0 AS waited_time,
‘’ AS apply_by,
‘’ AS apply_name,
‘’ AS apply_center_code,
‘’ AS apply_center_name,
ers.rec_company_name AS apply_company_name,
ers.rec_company_code AS apply_company_code,
ers.rec_company_code AS apply_company_id,
ers.rec_company_name AS company_name,
ers.rec_company_code AS company_code,
ers.rec_company_code AS company_id,
(SELECT bc.plate_name FROM bd_company bc where bc.company_code = ers.rec_company_code ) AS plate_name,
(SELECT bc.plate_code FROM bd_company bc where bc.company_code = ers.rec_company_code ) AS plate_code,
(SELECT bc.plate_id FROM bd_company bc where bc.company_code = ers.rec_company_code ) AS plate_id,
CASE
WHEN ers.claim_emp_id = ‘-1’ THEN
‘22222222222222222’ ELSE ers.claim_emp_id
END AS operator_id,
CASE
WHEN ers.claim_emp_name = ‘系统’ THEN
‘小财’ ELSE ers.claim_emp_name
END AS operator_name,
‘900000000900000001’ AS operator_group_id,
‘应收稽核组收款认领’ AS task_group_name,
‘BILL_POOL_TYPE’ AS pool_type_code,
YEAR ( ers.claim_date ) AS year_id,
YEAR ( ers.claim_date ) AS year_code,
CONCAT( YEAR ( ers.claim_date ), ‘年’ ) year_name,
MONTH ( ers.claim_date ) AS month_id,
‘AUDITED’ AS month_code,#修改为审批状态
CONCAT( MONTH ( ers.claim_date ), ‘月’ ) month_name,
DAY ( ers.claim_date ) AS day_id,
DAY ( ers.claim_date ) AS day_code,
CONCAT( DAY ( ers.claim_date ), ‘日’ ) AS day_name,
DATE_FORMAT( ers.claim_date, ‘%Y-%m-%d’ ) AS date_name,#日期
‘AUDITED’ AS order_status,
‘yingshou’ AS CurrentGroupId0,
‘应收审核组’ AS task_Group_Name0,
CASE

WHEN ers.claim_emp_code = ‘-1’ THEN
‘000000’ ELSE claim_emp_code
END AS employee_code
FROM
exp_receipt_serial ers
WHERE
ers.claim_status = ‘CLAIMED’
AND ers.claim_order_code IS NOT NULL
AND ers.claim_date > ‘2024-01-01 00:00:00’
AND ers.rec_company_code NOT IN (
SELECT
bdi.item_value
FROM
bd_dict bd,
bd_dict_item bdi
WHERE
bd.CODE = ‘RLD_NOT_IN_FSSC_COMPANY’
AND bd.dict_id = bdi.dict_id
AND bdi.STATUS = 1
AND ( bdi.attribute1 IS NULL OR bdi.attribute1 != ‘Y’ )
)"
]
}
]
}
},
“writer”: {
“name”: “mysqlwriter”,
“parameter”: {
“writeMode”: “insert”,
“username”: “
normal.REPORT.jdbc.username","password":"
normal.REPORT.jdbc.username","password":"{norm…ord}”,
“connection”: [
{
“jdbcUrl”: “${normal.REPORT.jdbc.url}”,
“table”: [
“m_fsop_productiveness_screen”
]
}
],
“preSql”: [
“delete from m_fsop_productiveness_screen;”
],
“column”: [
“reim_code”,
“cust_order_type”,
“bill_id”,
“bill_type_id”,
“cust_bill_type_code”,
“cust_bill_type_name”,
“log_start_time”,
“log_end_time”,
“waited_time”,
“apply_by”,
“apply_name”,
"apply_center_code
…(truncated)…

Now let me also update the MEMORY.md with this new report pattern.

已生成 ✅ 共 8 张物化视图，管道式结构：

dw_local 层（6张）
#	表名	作用
1	dim_company_plate	公司→事业部映射（替代收款池关联子查询）
2	dim_rld_not_in_fssc	排除公司列表（替代嵌套子查询）
3	dim_pool_type_bill	单据池类型过滤集（替代内关联）
4	m_fsop_productiveness_screen_log	工单稽核日志分支（UNION 上半部分）
5	m_fsop_productiveness_screen_skc	收款池分支（UNION 下半部分）
6	m_fsop_productiveness_screen	合并宽表（UNION ALL 以上两支）
dmk_local 层（2张）
#	表名	作用
7	m_fsop_productiveness_screen_agg_by_month	月度聚合（年月+公司+单据类型+工单类型 → 单据量/等待时长）
8	m_fsop_productiveness_screen_agg_by_operator	人员绩效聚合（年月+操作人员+组别 → 单据量/等待时长）
⚠ 注意
原 ETL 使用了 MySQL 自定义函数 MyDateSubWorkTime()（计算工作日差），Doris 中无此函数。当前 SQL 用 TIMESTAMPDIFF(MINUTE, ...) 作占位，你需要：

方案 A：在 Doris 中创建同名 UDF 实现工作日计算逻辑
方案 B：用纯 SQL 表达式替代（需考虑节假日、周末排除）
复制
点赞
点踩
朗读
分享
在新窗口打开
这是帮你整理的文档：

doris_fsop_productiveness_screen_mv.sql
SQL
丨
19.0 KB
方案调整：dw_local基本的宽表，dmk_local根据业务做单个功能的整合，dmr_local最终整合成的复杂功能需求。命名方案调整：dmr_local最终输出物化视图 命名dmr_sjp_fssc_fsop_productiveness_screen_mv,dmk_local命名dmk_sjp_fssc_fsop_productiveness_screen_xx_mv(xx为变量)；dw_local:dw_sjp_fssc_fsop_productiveness_screen_xx_xx1_mv(xx/xx1为变量).请根据以上方案进行调整

现在更新记忆。

也更新日报。

已调整完成 ✅ 新命名规范 + 三层架构：

命名方案
层	命名模式	示例
dw_local 基本宽表	dw_sjp_fssc_{report}_xx_xx1_mv	dw_sjp_fssc_fsop_productiveness_screen_main_log_mv
dmk_local 单功能聚合	dmk_sjp_fssc_{report}_xx_mv	dmk_sjp_fssc_fsop_productiveness_screen_agg_by_month_mv
dmr_local 最终整合输出	dmr_sjp_fssc_{report}_mv	dmr_sjp_fssc_fsop_productiveness_screen_mv
9张物化视图架构
code

复制
ODS 表
  ├─ dw_sjp_fssc_fsop_productiveness_screen_dim_company_plate_mv      ①
  ├─ dw_sjp_fssc_fsop_productiveness_screen_dim_rld_not_in_fssc_mv    ②
  └─ dw_sjp_fssc_fsop_productiveness_screen_dim_pool_type_bill_mv     ③
  ├─ dw_sjp_fssc_fsop_productiveness_screen_main_log_mv               ④
  └─ dw_sjp_fssc_fsop_productiveness_screen_main_skc_mv               ⑤
      └─ dw_sjp_fssc_fsop_productiveness_screen_main_all_mv           ⑥
          ├─ dmk_sjp_fssc_fsop_productiveness_screen_agg_by_month_mv   ⑦
          └─ dmk_sjp_fssc_fsop_productiveness_screen_agg_by_operator_mv ⑧
              └─ dmr_sjp_fssc_fsop_productiveness_screen_mv            ⑨
dmr 最终输出模块
dmr_sjp_fssc_fsop_productiveness_screen_mv 通过 report_type 字段区分三个模块：

report_type	来源	说明
monthly_trend	dmk agg_by_month	月度趋势（按月/公司/工单组）
operator_ranking	dmk agg_by_operator	人员绩效排行（按操作员/组别）
bill_type_summary	dw main_all 直算	单据类型维度汇总（新增）
前端只需查这一张 dmr 视图即可拿到全部大屏数据，report_type 过滤即得对应模块。
```

生成物化视图详情：

```
-- ============================================================================
-- Doris 物化视图定义 — 共享财务效能大屏报表
-- 来源：MySQL FSSC ETL → Hive ods_fssc → Doris 物化视图
-- 创建时间：2026-07-28
-- ============================================================================
--
-- 命名规范：
--   dw_local  基本宽表：dw_sjp_fssc_fsop_productiveness_screen_{branch}_{sub}_mv
--   dmk_local 单功能聚合：dmk_sjp_fssc_fsop_productiveness_screen_{feature}_mv
--   dmr_local 最终复杂整合：dmr_sjp_fssc_fsop_productiveness_screen_mv
--
-- 管道架构：
--   ODS 表（hive.ods_fssc.*）
--     ├─ dim_company_plate  事业部维度
--     ├─ dim_rld_not_in_fssc  排除公司过滤集
--     └─ dim_pool_type_bill  单据池过滤集
--     ├─ main_log  工单稽核日志分支（UNION 上半）
--     ├─ main_skc  收款池分支（UNION 下半）
--     └─ main_all  合并宽表（UNION ALL）
--     ├─ agg_by_month   单功能：月度趋势聚合
--     ├─ agg_by_operator 单功能：人员绩效聚合
--     └─ dmr_sjp_fssc_fsop_productiveness_screen_mv 最终整合输出
--
-- ⚠ 注意：原 ETL 使用了 MySQL 自定义函数 MyDateSubWorkTime()，
--    Doris 中需替换为自定义 UDF 或 SQL 表达式。此处保留占位注释。
-- ============================================================================

-- ============================================================================
-- dw_local 层 — 基本宽表
-- ============================================================================

-- ----------------------------------------------------------------------------
-- 1. dim_company_plate — 公司→事业部映射
--    替代原 ETL 中 bd_company 左关联及收款池分支的关联子查询
-- ----------------------------------------------------------------------------

CREATE MATERIALIZED VIEW IF NOT EXISTS dw_local.dw_sjp_fssc_fsop_productiveness_screen_dim_company_plate_mv
DUPLICATE KEY(company_code)
DISTRIBUTED BY HASH(company_code)
BUCKETS AUTO
REFRESH ASYNC EVERY(INTERVAL 1 DAY)
PROPERTIES ("replication_num" = "3")
AS
SELECT
    company_code,
    plate_name,
    plate_code,
    plate_id
FROM hive.ods_fssc.bd_company;

-- ----------------------------------------------------------------------------
-- 2. dim_rld_not_in_fssc — 收款池排除公司列表
--    dict_code = 'RLD_NOT_IN_FSSC_COMPANY'，status = 1，attribute1 != 'Y'
-- ----------------------------------------------------------------------------

CREATE MATERIALIZED VIEW IF NOT EXISTS dw_local.dw_sjp_fssc_fsop_productiveness_screen_dim_rld_not_in_fssc_mv
DUPLICATE KEY(item_value)
DISTRIBUTED BY HASH(item_value)
BUCKETS AUTO
REFRESH ASYNC EVERY(INTERVAL 1 DAY)
PROPERTIES ("replication_num" = "3")
AS
SELECT
    bdi.item_value
FROM
    hive.ods_fssc.bd_dict_item bdi
INNER JOIN
    hive.ods_fssc.bd_dict bd
    ON bd.dict_id = bdi.dict_id
WHERE
    bd.code = 'RLD_NOT_IN_FSSC_COMPANY'
    AND bdi.status = 1
    AND IFNULL(bdi.attribute1, '') != 'Y';

-- ----------------------------------------------------------------------------
-- 3. dim_pool_type_bill — 单据池过滤集
--    pool_type_code = 'BILL_POOL_TYPE' 的 bill_id 集合
-- ----------------------------------------------------------------------------

CREATE MATERIALIZED VIEW IF NOT EXISTS dw_local.dw_sjp_fssc_fsop_productiveness_screen_dim_pool_type_bill_mv
DUPLICATE KEY(bill_id)
DISTRIBUTED BY HASH(bill_id)
BUCKETS AUTO
REFRESH ASYNC EVERY(INTERVAL 1 DAY)
PROPERTIES ("replication_num" = "3")
AS
SELECT DISTINCT bill_id
FROM hive.ods_fssc.fsop_tm_task_pool
WHERE pool_type_code = 'BILL_POOL_TYPE';

-- ----------------------------------------------------------------------------
-- 4. main_log — 工单稽核日志分支（UNION 上半）
--    来源：fsop_tm_log + fsop_tm_task_pool + fsop_tm_bill_type
--          + fsop_tm_task_group + exp_base_reim + bd_company + bd_employee
--    过滤：NODE_ACCOUNTING_AUDIT / APPROVE / operator_id != -1 / 指定单据类型
-- ----------------------------------------------------------------------------

CREATE MATERIALIZED VIEW IF NOT EXISTS dw_local.dw_sjp_fssc_fsop_productiveness_screen_main_log_mv
DUPLICATE KEY(
    reim_code,
    bill_id,
    log_end_time,
    year_code,
    month_code
)
DISTRIBUTED BY HASH(company_code, year_code, month_code)
BUCKETS AUTO
REFRESH ASYNC EVERY(INTERVAL 1 DAY)
PROPERTIES ("replication_num" = "3")
AS
SELECT
    ebr.reim_code,
    ebr.cust_order_type,
    ftl.bill_id,
    ftl.bill_type_id,
    ftbt.cust_bill_type_code,
    ftbt.cust_bill_type_name,
    ftl.log_start_time,
    ftl.log_end_time,

-- ⚠ 原 ETL: MyDateSubWorkTime(TIMESTAMP(ftl.log_start_time), TIMESTAMP(ftl.log_end_time)) / 60
    -- Doris 中需替换为自定义 UDF 或工作日计算表达式，此处暂用分钟差占位
    TIMESTAMPDIFF(MINUTE, ftl.log_start_time, ftl.log_end_time) / 60.0 AS waited_time,

ebr.apply_by,
    ebr.apply_name,
    ebr.apply_center_code,
    ebr.apply_center_name,
    ebr.apply_company_name,
    ebr.apply_company_code,
    ebr.apply_company_id,
    ebr.company_name,
    ebr.company_code,
    ebr.company_id,
    temp.plate_name,
    temp.plate_code,
    temp.plate_id,
    ftl.operator_id,
    ftl.operator_name,
    ftl.operator_group_id,
    fttg.task_group_name,
    fttg.pool_type_code,
    YEAR(ftl.log_end_time)                                          AS year_id,
    YEAR(ftl.log_end_time)                                          AS year_code,
    CONCAT(YEAR(ftl.log_end_time), '年')                            AS year_name,
    MONTH(ftl.log_end_time)                                         AS month_id,
    ebr.order_status                                                AS month_code,
    CONCAT(MONTH(ftl.log_end_time), '月')                           AS month_name,
    DAY(ftl.log_end_time)                                           AS day_id,
    DAY(ftl.log_end_time)                                           AS day_code,
    CONCAT(DAY(ftl.log_end_time), '日')                             AS day_name,
    DATE_FORMAT(ftl.log_end_time, '%Y-%m-%d')                       AS date_name,
    ebr.order_status                                                AS order_status,
    CASE
        WHEN ftbt.cust_bill_type_code IN ('CA_MANUAL', 'CA_ACCOUNT_APPLY')
            THEN 'zongzhang'
        WHEN ftbt.cust_bill_type_code IN ('PA_ZX_YF', 'PA_ZX_YFYF')
            THEN 'yingfu'
        WHEN ftbt.cust_bill_type_code IN ('RF_KP', 'CA_JDX', 'CA_JDXPD', 'CL_CX')
            THEN 'yingshou'
        ELSE 'feiyong'
    END                                                             AS CurrentGroupId0,
    CASE
        WHEN ftbt.cust_bill_type_code IN ('CA_MANUAL', 'CA_ACCOUNT_APPLY')
            THEN '总账工单组'
        WHEN ftbt.cust_bill_type_code IN ('PA_ZX_YF', 'PA_ZX_YFYF')
            THEN '应付审核组'
        WHEN ftbt.cust_bill_type_code IN ('RF_KP', 'CA_JDX', 'CA_JDXPD', 'CL_CX')
            THEN '应收审核组'
        ELSE '费用审核组'
    END                                                             AS task_Group_Name0,
    be.employee_code
FROM
    hive.ods_fssc.fsop_tm_log ftl
INNER JOIN
    dw_local.dw_sjp_fssc_fsop_productiveness_screen_dim_pool_type_bill_mv ptb
    ON ptb.bill_id = ftl.bill_id
INNER JOIN
    hive.ods_fssc.fsop_tm_bill_type ftbt
    ON ftbt.bill_type_id = ftl.bill_type_id
INNER JOIN
    hive.ods_fssc.fsop_tm_task_group fttg
    ON fttg.task_group_id = ftl.operator_group_id
INNER JOIN
    hive.ods_fssc.exp_base_reim ebr
    ON ebr.reim_code = ftl.bill_code
LEFT JOIN
    dw_local.dw_sjp_fssc_fsop_productiveness_screen_dim_company_plate_mv temp
    ON temp.company_code = ebr.company_code
LEFT JOIN
    hive.ods_fssc.bd_employee be
    ON be.employee_id = ftl.operator_id
WHERE
    ftl.current_node_code = 'NODE_ACCOUNTING_AUDIT'
    AND ftl.task_operate_type = 'APPROVE'
    AND ftl.operator_id != -1
    AND ftbt.cust_bill_type_code IN (
        'EC_CL', 'EC_TY', 'EC_SPECIAL', 'EC_SL',
        'PA_LOAN', 'CL_RP', 'PA_PREPAY', 'EC_EB_MC',
        'EC_EB_FC', 'EC_CCS', 'EC_SB', 'PA_ZX_YF',
        'PA_ZX_YFYF', 'CA_YT', 'PA_ZX_CUR', 'EC_MARKETING',
        'CA_MANUAL', 'CA_ACCOUNT_APPLY', 'RF_KP', 'CA_JDX',
        'CA_JDXPD', 'EC_TMS'
    );

-- ----------------------------------------------------------------------------
-- 5. main_skc — 收款池分支（UNION 下半）
--    来源：exp_receipt_serial
--    过滤：claim_status = 'CLAIMED' / claim_order_code IS NOT NULL
--          / claim_date > '2024-01-01' / 排除公司
-- ----------------------------------------------------------------------------

CREATE MATERIALIZED VIEW IF NOT EXISTS dw_local.dw_sjp_fssc_fsop_productiveness_screen_main_skc_mv
DUPLICATE KEY(
    reim_code,
    bill_id,
    log_end_time,
    year_code,
    month_code
)
DISTRIBUTED BY HASH(company_code, year_code, month_code)
BUCKETS AUTO
REFRESH ASYNC EVERY(INTERVAL 1 DAY)
PROPERTIES ("replication_num" = "3")
AS
SELECT
    ers.claim_order_code                                            AS reim_code,
    'SKC'                                                           AS cust_order_type,
    ers.receipt_serial_id                                           AS bill_id,
    ''                                                              AS bill_type_id,
    'SKC'                                                           AS cust_bill_type_code,
    '收款池'                                                         AS cust_bill_type_name,
    ers.claim_date                                                  AS log_start_time,
    ers.claim_date                                                  AS log_end_time,
    0                                                               AS waited_time,
    ''                                                              AS apply_by,
    ''                                                              AS apply_name,
    ''                                                              AS apply_center_code,
    ''                                                              AS apply_center_name,
    ers.rec_company_name                                            AS apply_company_name,
    ers.rec_company_code                                            AS apply_company_code,
    ers.rec_company_code                                            AS apply_company_id,
    ers.rec_company_name                                            AS company_name,
    ers.rec_company_code                                            AS company_code,
    ers.rec_company_code                                            AS company_id,
    cp.plate_name,
    cp.plate_code,
    cp.plate_id,
    CASE
        WHEN ers.claim_emp_id = '-1' THEN '22222222222222222'
        ELSE ers.claim_emp_id
    END                                                             AS operator_id,
    CASE
        WHEN ers.claim_emp_name = '系统' THEN '小财'
        ELSE ers.claim_emp_name
    END                                                             AS operator_name,
    '900000000900000001'                                            AS operator_group_id,
    '应收稽核组收款认领'                                             AS task_group_name,
    'BILL_POOL_TYPE'                                                AS pool_type_code,
    YEAR(ers.claim_date)                                            AS year_id,
    YEAR(ers.claim_date)                                            AS year_code,
    CONCAT(YEAR(ers.claim_date), '年')                              AS year_name,
    MONTH(ers.claim_date)                                           AS month_id,
    'AUDITED'                                                       AS month_code,
    CONCAT(MONTH(ers.claim_date), '月')                             AS month_name,
    DAY(ers.claim_date)                                             AS day_id,
    DAY(ers.claim_date)                                             AS day_code,
    CONCAT(DAY(ers.claim_date), '日')                               AS day_name,
    DATE_FORMAT(ers.claim_date, '%Y-%m-%d')                         AS date_name,
    'AUDITED'                                                       AS order_status,
    'yingshou'                                                      AS CurrentGroupId0,
    '应收审核组'                                                     AS task_Group_Name0,
    CASE
        WHEN ers.claim_emp_code = '-1' THEN '000000'
        ELSE ers.claim_emp_code
    END                                                             AS employee_code
FROM
    hive.ods_fssc.exp_receipt_serial ers
LEFT JOIN
    dw_local.dw_sjp_fssc_fsop_productiveness_screen_dim_company_plate_mv cp
    ON cp.company_code = ers.rec_company_code
WHERE
    ers.claim_status = 'CLAIMED'
    AND ers.claim_order_code IS NOT NULL
    AND ers.claim_date > '2024-01-01 00:00:00'
    AND ers.rec_company_code NOT IN (
        SELECT item_value FROM dw_local.dw_sjp_fssc_fsop_productiveness_screen_dim_rld_not_in_fssc_mv
    );

-- ----------------------------------------------------------------------------
-- 6. main_all — 合并宽表（UNION ALL：工单日志 + 收款池）
--    等价于 ETL 的 UNION 结果
-- ----------------------------------------------------------------------------

CREATE MATERIALIZED VIEW IF NOT EXISTS dw_local.dw_sjp_fssc_fsop_productiveness_screen_main_all_mv
DUPLICATE KEY(
    reim_code,
    bill_id,
    log_end_time,
    year_code,
    month_code
)
DISTRIBUTED BY HASH(company_code, year_code, month_code)
BUCKETS AUTO
REFRESH ASYNC EVERY(INTERVAL 1 DAY)
PROPERTIES ("replication_num" = "3")
AS
SELECT * FROM dw_local.dw_sjp_fssc_fsop_productiveness_screen_main_log_mv
UNION ALL
SELECT * FROM dw_local.dw_sjp_fssc_fsop_productiveness_screen_main_skc_mv;

-- ============================================================================
-- dmk_local 层 — 单功能聚合
-- ============================================================================

-- ----------------------------------------------------------------------------
-- 7. agg_by_month — 月度趋势聚合
--    维度：年月 + 公司 + 单据类型 + 工单组
--    指标：单据量 / 等待时长合计 / 平均等待时长
-- ----------------------------------------------------------------------------

CREATE MATERIALIZED VIEW IF NOT EXISTS dmk_local.dmk_sjp_fssc_fsop_productiveness_screen_agg_by_month_mv
AGGREGATE KEY(
    year_code,
    month_code,
    company_code,
    company_name,
    cust_bill_type_code,
    cust_bill_type_name,
    CurrentGroupId0,
    task_Group_Name0
)
DISTRIBUTED BY HASH(company_code, year_code, month_code)
BUCKETS AUTO
REFRESH ASYNC EVERY(INTERVAL 1 DAY)
PROPERTIES ("replication_num" = "3")
AS
SELECT
    year_code,
    month_code,
    company_code,
    company_name,
    cust_bill_type_code,
    cust_bill_type_name,
    CurrentGroupId0,
    task_Group_Name0,
    COUNT(1)               AS bill_count,
    ROUND(SUM(waited_time), 2)  AS total_waited_hours,
    ROUND(AVG(waited_time), 2)  AS avg_waited_hours
FROM
    dw_local.dw_sjp_fssc_fsop_productiveness_screen_main_all_mv
GROUP BY
    year_code,
    month_code,
    company_code,
    company_name,
    cust_bill_type_code,
    cust_bill_type_name,
    CurrentGroupId0,
    task_Group_Name0;

-- ----------------------------------------------------------------------------
-- 8. agg_by_operator — 人员绩效聚合
--    维度：年月 + 操作人员 + 组别 + 工单组
--    指标：单据量 / 等待时长合计 / 平均等待时长
-- ----------------------------------------------------------------------------

CREATE MATERIALIZED VIEW IF NOT EXISTS dmk_local.dmk_sjp_fssc_fsop_productiveness_screen_agg_by_operator_mv
AGGREGATE KEY(
    year_code,
    month_code,
    operator_id,
    operator_name,
    task_group_name,
    CurrentGroupId0
)
DISTRIBUTED BY HASH(operator_id, year_code, month_code)
BUCKETS AUTO
REFRESH ASYNC EVERY(INTERVAL 1 DAY)
PROPERTIES ("replication_num" = "3")
AS
SELECT
    year_code,
    month_code,
    operator_id,
    operator_name,
    task_group_name,
    CurrentGroupId0,
    COUNT(1)               AS bill_count,
    ROUND(SUM(waited_time), 2)  AS total_waited_hours,
    ROUND(AVG(waited_time), 2)  AS avg_waited_hours
FROM
    dw_local.dw_sjp_fssc_fsop_productiveness_screen_main_all_mv
GROUP BY
    year_code,
    month_code,
    operator_id,
    operator_name,
    task_group_name,
    CurrentGroupId0;

-- ============================================================================
-- dmr_local 层 — 最终复杂功能整合输出
-- ============================================================================

-- ----------------------------------------------------------------------------
-- 9. dmr_sjp_fssc_fsop_productiveness_screen_mv
--    大屏综合输出：整合月度趋势 + 人员绩效 + 明细数据
--    前端只需查此一张视图即可获得全部大屏所需指标
-- ----------------------------------------------------------------------------

CREATE MATERIALIZED VIEW IF NOT EXISTS dmr_local.dmr_sjp_fssc_fsop_productiveness_screen_mv
DUPLICATE KEY(
    report_type,
    year_code,
    month_code
)
DISTRIBUTED BY HASH(report_type, year_code, month_code)
BUCKETS AUTO
REFRESH ASYNC EVERY(INTERVAL 1 DAY)
PROPERTIES ("replication_num" = "3")
AS

-- === 模块1: 月度趋势数据 ===
SELECT
    'monthly_trend'        AS report_type,
    year_code,
    month_code,
    company_code,
    company_name,
    ''                     AS cust_bill_type_code,
    ''                     AS cust_bill_type_name,
    ''                     AS CurrentGroupId0,
    ''                     AS task_Group_Name0,
    ''                     AS operator_id,
    ''                     AS operator_name,
    ''                     AS task_group_name,
    bill_count,
    total_waited_hours,
    avg_waited_hours
FROM
    dmk_local.dmk_sjp_fssc_fsop_productiveness_screen_agg_by_month_mv

UNION ALL

-- === 模块2: 人员绩效排行 ===
SELECT
    'operator_ranking'     AS report_type,
    year_code,
    month_code,
    ''                     AS company_code,
    ''                     AS company_name,
    ''                     AS cust_bill_type_code,
    ''                     AS cust_bill_type_name,
    CurrentGroupId0,
    task_Group_Name0,
    operator_id,
    operator_name,
    task_group_name,
    bill_count,
    total_waited_hours,
    avg_waited_hours
FROM
    dmk_local.dmk_sjp_fssc_fsop_productiveness_screen_agg_by_operator_mv

UNION ALL

-- === 模块3: 单据类型维度汇总（新增）===
SELECT
    'bill_type_summary'    AS report_type,
    year_code,
    month_code,
    ''                     AS company_code,
    ''                     AS company_name,
    cust_bill_type_code,
    cust_bill_type_name,
    CurrentGroupId0,
    task_Group_Name0,
    ''                     AS operator_id,
    ''                     AS operator_name,
    ''                     AS task_group_name,
    COUNT(1)               AS bill_count,
    ROUND(SUM(waited_time), 2)  AS total_waited_hours,
    ROUND(AVG(waited_time), 2)  AS avg_waited_hours
FROM
    dw_local.dw_sjp_fssc_fsop_productiveness_screen_main_all_mv
GROUP BY
    year_code,
    month_code,
    cust_bill_type_code,
    cust_bill_type_name,
    CurrentGroupId0,
    task_Group_Name0;

```

做一些修改，在doris执行即可完成相关报表开发。大大加快了开发速度