如何在ClickHouse中用SQL实现上月求和指标并动态生成查询语句

一、获取PreviousMonthValue的两种SQL写法 #

方法1：使用窗口函数LAG #

利用LAG()窗口函数，在同一objectCode分组内按月份排序，直接取上一个月的聚合值：

select 
    toStartOfMonth(Period) as pt, 
    objectCode, 
    sum(amount) as ThisMonthValue,
    -- 取同objectCode分组内上一个月份的sum(amount)，首月值为NULL，可按需用IFNULL转为0
    IFNULL(LAG(sum(amount)) OVER (PARTITION BY objectCode ORDER BY pt), 0) as PreviousMonthValue
from posts
group by pt, objectCode
order by objectCode, pt

方法2：自关联预聚合数据 #

先预聚合每月数据，再通过左连接关联上月数据：

with monthly_agg as (
    select 
        toStartOfMonth(Period) as pt, 
        objectCode, 
        sum(amount) as ThisMonthValue
    from posts
    group by pt, objectCode
)
select 
    m1.pt,
    m1.objectCode,
    m1.ThisMonthValue,
    -- 用COALESCE将无上月数据的情况转为0
    COALESCE(m2.ThisMonthValue, 0) as PreviousMonthValue
from monthly_agg m1
left join monthly_agg m2 
    on m1.objectCode = m2.objectCode 
    and m1.pt = addMonths(m2.pt, 1)
order by m1.objectCode, m1.pt

二、动态生成SQL的实现思路 #

将指标定义（如聚合表达式、别名）存储在配置文件中，通过编程语言读取配置并拼接SQL，无需硬编码修改SQL语句：

1. 配置文件示例（JSON格式） #

{
  "base_fields": ["toStartOfMonth(Period) as pt", "objectCode"],
  "metrics": [
    {"alias": "ThisMonthValue", "expression": "sum(amount)"},
    {"alias": "PreviousMonthValue", "expression": "IFNULL(LAG(sum(amount)) OVER (PARTITION BY objectCode ORDER BY pt), 0)"}
  ],
  "group_by_fields": ["pt", "objectCode"]
}

2. 编程语言拼接SQL示例（Python） #

import json

# 读取配置文件
with open("metric_config.json", "r", encoding="utf-8") as f:
    config = json.load(f)

# 拼接SELECT部分
select_clause = ", ".join(config["base_fields"] + [f"{m['expression']} as {m['alias']}" for m in config["metrics"]])
# 拼接GROUP BY部分
group_by_clause = ", ".join(config["group_by_fields"])

# 生成最终SQL
final_sql = f"""
select {select_clause}
from posts
group by {group_by_clause}
order by objectCode, pt
"""

print(final_sql)

后续如需调整指标（比如新增其他聚合项、修改上月值的计算逻辑），只需修改配置文件即可，无需改动代码中的SQL模板。

内容的提问来源于stack exchange，提问作者Hengaini