IOMETE环境下YTD交易占比计算及查询合并的SQL实现

需求说明 #

在IOMETE数据库中统计**年初至今（YTD）**的交易数据，按卡类（Card）、银行（Bank）交易类型分别计算：

• 交易金额占比（Payment Value）
• 交易笔数占比（Usage）
  同时实现该查询结果与其他已完成数据过滤的查询执行UNION合并。

示例数据 #

期望结果 #

修正后的YTD占比计算SQL #

原代码存在CTE命名不匹配、仅统计笔数占比、硬编码年份等问题，以下是优化后的查询：

WITH filtered_payments AS (
    SELECT 
        payment_method AS 交易类型,
        amount AS 金额
    FROM production.payments
    WHERE created_at >= DATE_TRUNC('year', CURRENT_DATE)  -- 动态取当年年初，自动适配YTD
      AND payment_method IN ('Card', 'Bank')  -- 仅保留目标交易类型
      AND status != 'failed'  -- 排除失败交易
)
SELECT 
    交易类型,
    ROUND(SUM(金额) / SUM(SUM(金额)) OVER () * 100, 2) || '%' AS 金额占比,
    ROUND(COUNT(*) / SUM(COUNT(*)) OVER () * 100, 2) || '%' AS 笔数占比
FROM filtered_payments
GROUP BY 交易类型;

关键说明： #

• 用DATE_TRUNC('year', CURRENT_DATE)替代硬编码年份，自动获取当年1月1日，适配每年的YTD统计
• 同时计算金额、笔数的全局占比，通过窗口函数SUM(...) OVER ()获取总行数/总金额
• 拼接%符号，直接输出带百分比的结果

与其他查询的UNION操作 #

执行UNION需满足：两个查询的列数一致、列数据类型匹配，列名以第一个查询为准。

假设已有一个过滤完成的查询other_filtered_data，结构与上述结果一致（交易类型、金额占比、笔数占比），合并写法如下：

-- 第一个查询：YTD占比统计
WITH filtered_payments AS (
    SELECT 
        payment_method AS 交易类型,
        amount AS 金额
    FROM production.payments
    WHERE created_at >= DATE_TRUNC('year', CURRENT_DATE)
      AND payment_method IN ('Card', 'Bank')
      AND status != 'failed'
)
SELECT 
    交易类型,
    ROUND(SUM(金额) / SUM(SUM(金额)) OVER () * 100, 2) || '%' AS 金额占比,
    ROUND(COUNT(*) / SUM(COUNT(*)) OVER () * 100, 2) || '%' AS 笔数占比
FROM filtered_payments
GROUP BY 交易类型

-- 使用UNION ALL保留重复行（性能更优）；若需去重则替换为UNION
UNION ALL

-- 第二个查询：已完成过滤的其他数据集
SELECT 
    交易类型,
    金额占比,
    笔数占比
FROM other_filtered_data;

内容的提问来源于stack exchange，提问作者E A S