sql - 是否有更有效的方法来编写 SQL 以按日期和列值对计数进行分组

Question

H2我有一个定期存储在数据库中的状态值 -1 或 1 ，我需要计算过去 12 个月按月存储的 -1 和 1 的数量。以下代码有效，但我将在多个地方将其用作派生表，并想知道是否有更有效的方法。

SELECT  STATUS_CODE AS STATUS,
    COUNT(*) AS STATUS_COUNT,
    CASE    
        WHEN EXTRACT(EPOCH FROM DATEADD(MONTH,-1,CURRENT_DATE)) < EXTRACT(EPOCH FROM LOG_ENTRY_TIME)   THEN 'MONTH 1'
        WHEN EXTRACT(EPOCH FROM DATEADD(MONTH,-2,CURRENT_DATE)) < EXTRACT(EPOCH FROM LOG_ENTRY_TIME)   THEN 'MONTH 2'
        WHEN EXTRACT(EPOCH FROM DATEADD(MONTH,-3,CURRENT_DATE)) < EXTRACT(EPOCH FROM LOG_ENTRY_TIME)   THEN 'MONTH 3'
        WHEN EXTRACT(EPOCH FROM DATEADD(MONTH,-4,CURRENT_DATE)) < EXTRACT(EPOCH FROM LOG_ENTRY_TIME)   THEN 'MONTH 4'
        WHEN EXTRACT(EPOCH FROM DATEADD(MONTH,-5,CURRENT_DATE)) < EXTRACT(EPOCH FROM LOG_ENTRY_TIME)   THEN 'MONTH 5'
        WHEN EXTRACT(EPOCH FROM DATEADD(MONTH,-6,CURRENT_DATE)) < EXTRACT(EPOCH FROM LOG_ENTRY_TIME)   THEN 'MONTH 6'
        WHEN EXTRACT(EPOCH FROM DATEADD(MONTH,-7,CURRENT_DATE)) < EXTRACT(EPOCH FROM LOG_ENTRY_TIME)   THEN 'MONTH 7'
        WHEN EXTRACT(EPOCH FROM DATEADD(MONTH,-8,CURRENT_DATE)) < EXTRACT(EPOCH FROM LOG_ENTRY_TIME)   THEN 'MONTH 8'
        WHEN EXTRACT(EPOCH FROM DATEADD(MONTH,-9,CURRENT_DATE)) < EXTRACT(EPOCH FROM LOG_ENTRY_TIME)   THEN 'MONTH 9'
        WHEN EXTRACT(EPOCH FROM DATEADD(MONTH,-10,CURRENT_DATE)) < EXTRACT(EPOCH FROM LOG_ENTRY_TIME)   THEN 'MONTH 10'
        WHEN EXTRACT(EPOCH FROM DATEADD(MONTH,-11,CURRENT_DATE)) < EXTRACT(EPOCH FROM LOG_ENTRY_TIME)   THEN 'MONTH 11'
        WHEN EXTRACT(EPOCH FROM DATEADD(MONTH,-12,CURRENT_DATE)) < EXTRACT(EPOCH FROM LOG_ENTRY_TIME)   THEN 'MONTH 12'
        ELSE 'DONE' 
    END AS WEEK_RANGE
FROM    MY_TABLE 
WHERE   EXTRACT(EPOCH FROM DATEADD(MONTH,-12,CURRENT_DATE)) < EXTRACT(EPOCH FROM LOG_ENTRY_TIME) 
GROUP BY STATUS_CODE,  WEEK_RANGE
order by week_range

运行前面代码的结果。

标签： sqlh2

Answer 1

您可以使用以月为单位动态计算与当前日期DATEDIFF()之间的差异：log_entry_time

SELECT  
    status_code AS status,
    COUNT(*) AS status_count,
    DATEDIFF(MONTH, log_entry_time, CURRENT_DATE) month_range
FROM MY_TABLE 
WHERE log_entry_time  >= DATEADD(MONTH, -12, CURRENT_DATE)
GROUP BY status_code, month_range
ORDER BY month_range

如果LOG_ENTRY_TIME是date-like 数据类型，请不要将其转换为 epoch 进行比较，因为这样做会阻止在该列上使用索引。您可以改为进行日期比较，如WHERE上述查询的子句所示。