如何在SQL中结合Dense_Rank分区与日期季度判定逻辑

实现方案 #

你可以直接把季度计算的CASE WHEN逻辑合并后加入SELECT列表，同时根据实际业务需求调整窗口函数的分区规则即可，有两种常用场景可供选择：

场景1：保留原全年级别的统计，同时新增季度维度的统计 #

如果需要同时保留原有的按性别全局分组的结果、又要增加季度维度的分组计算，代码如下：

SELECT DISTINCT
        Gender
        -- 合并后的季度计算逻辑
      , CASE 
            WHEN Date BETWEEN '2020-01-01' AND '2020-03-31' THEN 'Q1_2020'
            WHEN Date BETWEEN '2020-04-01' AND '2020-06-30' THEN 'Q2_2020'
            WHEN Date BETWEEN '2020-07-01' AND '2020-09-30' THEN 'Q3_2020'
            WHEN Date BETWEEN '2020-10-01' AND '2020-12-31' THEN 'Q4_2020'
        END AS Q_year
        -- 原有全年级别Status计算
      , dense_rank() over (partition by Gender order by ID) 
          + dense_rank() over (partition by Gender order by ID desc)
          - 1 AS Year_Status
        -- 新增按性别+季度分组的Status计算
      , dense_rank() over (partition by Gender, CASE 
                                                    WHEN Date BETWEEN '2020-01-01' AND '2020-03-31' THEN 'Q1_2020'
                                                    WHEN Date BETWEEN '2020-04-01' AND '2020-06-30' THEN 'Q2_2020'
                                                    WHEN Date BETWEEN '2020-07-01' AND '2020-09-30' THEN 'Q3_2020'
                                                    WHEN Date BETWEEN '2020-10-01' AND '2020-12-31' THEN 'Q4_2020'
                                                END order by ID) 
          + dense_rank() over (partition by Gender, CASE 
                                                        WHEN Date BETWEEN '2020-01-01' AND '2020-03-31' THEN 'Q1_2020'
                                                        WHEN Date BETWEEN '2020-04-01' AND '2020-06-30' THEN 'Q2_2020'
                                                        WHEN Date BETWEEN '2020-07-01' AND '2020-09-30' THEN 'Q3_2020'
                                                        WHEN Date BETWEEN '2020-10-01' AND '2020-12-31' THEN 'Q4_2020'
                                                    END order by ID desc)
          - 1 AS Quarter_Status
        -- 原有全年级别薪资汇总
      , SUM (Salary) OVER (PARTITION BY Gender) AS Year_Amount
        -- 新增按性别+季度分组的薪资汇总
      , SUM (Salary) OVER (PARTITION BY Gender, CASE 
                                                    WHEN Date BETWEEN '2020-01-01' AND '2020-03-31' THEN 'Q1_2020'
                                                    WHEN Date BETWEEN '2020-04-01' AND '2020-06-30' THEN 'Q2_2020'
                                                    WHEN Date BETWEEN '2020-07-01' AND '2020-09-30' THEN 'Q3_2020'
                                                    WHEN Date BETWEEN '2020-10-01' AND '2020-12-31' THEN 'Q4_2020'
                                                END) AS Quarter_Amount
FROM Database1  
WHERE Date between '2020-01-01' and '2020-12-31'
ORDER BY Gender, Q_year

场景2：仅需要按性别+季度分组的统计结果 #

如果不需要保留全年级别统计，只需要按季度拆分后的分组结果，也可以用CTE先计算季度字段简化代码：

WITH temp_data AS (
    SELECT 
        *
        , CASE 
            WHEN Date BETWEEN '2020-01-01' AND '2020-03-31' THEN 'Q1_2020'
            WHEN Date BETWEEN '2020-04-01' AND '2020-06-30' THEN 'Q2_2020'
            WHEN Date BETWEEN '2020-07-01' AND '2020-09-30' THEN 'Q3_2020'
            WHEN Date BETWEEN '2020-10-01' AND '2020-12-31' THEN 'Q4_2020'
        END AS Q_year
    FROM Database1
    WHERE Date between '2020-01-01' and '2020-12-31'
)
SELECT DISTINCT
        Gender
      , Q_year
      , dense_rank() over (partition by Gender, Q_year order by ID) 
          + dense_rank() over (partition by Gender, Q_year order by ID desc)
          - 1 AS Status
      , SUM (Salary) OVER (PARTITION BY Gender, Q_year) AS Amount
FROM temp_data
ORDER BY Gender, Q_year

注意事项 #

• 你原有代码里的Salery是拼写错误，正确拼写应为Salary，建议同步修正避免报错
• DENSE_RANK的逻辑不需要调整，只需要在PARTITION BY子句中新增需要分组的维度（也就是季度字段）即可适配重复值去重的需求
• 如果你的SQL方言支持DATE_TRUNC类时间函数，季度计算可以简化为CONCAT('Q', EXTRACT(QUARTER FROM Date), '_', EXTRACT(YEAR FROM Date)) AS Q_year，不需要写冗长的CASE WHEN判断

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