求助：基于OLTP数据库，使用SQL Cube函数实现日期范围统计查询

针对4亿条数据的OLTP库，你原来用UNION多次扫描表的方案效率极低，完全不适合大数据量场景。下面提供两种更高效的实现方式，以及关于CUBE函数的说明：

一、直接分组统计（推荐，单表扫描） #

如果需要统计分段区间（如11-20年、21-50年、>50年），或者你要的>10/>20/>50是累计阈值统计，都可以用CASE表达式配合一次分组完成：

1. 分段区间统计（非累计） #

适合需要明确各区间独立计数的场景：

SELECT
  COUNT(*) AS "Count(*)",
  CASE
    WHEN DATEDIFF(year, Start_date, End_Date) > 50 THEN '> 50 yrs'
    WHEN DATEDIFF(year, Start_date, End_Date) > 20 THEN '21-50 yrs'
    WHEN DATEDIFF(year, Start_date, End_Date) > 10 THEN '11-20 yrs'
    ELSE '<= 10 yrs'
  END AS "DateRange Using DateDiff"
FROM your_table
GROUP BY
  CASE
    WHEN DATEDIFF(year, Start_date, End_Date) > 50 THEN '> 50 yrs'
    WHEN DATEDIFF(year, Start_date, End_Date) > 20 THEN '21-50 yrs'
    WHEN DATEDIFF(year, Start_date, End_Date) > 10 THEN '11-20 yrs'
    ELSE '<= 10 yrs'
  END
ORDER BY
  CASE
    WHEN DATEDIFF(year, Start_date, End_Date) > 50 THEN 3
    WHEN DATEDIFF(year, Start_date, End_Date) > 20 THEN 2
    WHEN DATEDIFF(year, Start_date, End_Date) > 10 THEN 1
    ELSE 0
  END DESC;

2. 累计阈值统计（如>10/20/50年的总数） #

如果需要每个阈值以上的累计数量（比如>50年的记录同时计入>20和>10年的统计），可以用SUM(CASE...)配合UNION ALL（仅扫描一次表）：

SELECT
  SUM(CASE WHEN DATEDIFF(year, Start_date, End_Date) > 10 THEN 1 ELSE 0 END) AS "Count(*)",
  '> 10 yrs' AS "DateRange Using DateDiff"
FROM your_table
UNION ALL
SELECT
  SUM(CASE WHEN DATEDIFF(year, Start_date, End_Date) > 20 THEN 1 ELSE 0 END),
  '> 20 yrs'
FROM your_table
UNION ALL
SELECT
  SUM(CASE WHEN DATEDIFF(year, Start_date, End_Date) > 50 THEN 1 ELSE 0 END),
  '> 50 yrs'
FROM your_table;

注：部分数据库支持用VALUES子句生成区间，再关联统计，进一步减少代码重复，比如SQL Server：

WITH thresholds AS (
  SELECT '> 10 yrs' AS range, 10 AS min_years UNION ALL
  SELECT '> 20 yrs', 20 UNION ALL
  SELECT '> 50 yrs', 50
)
SELECT
  COUNT(*) AS "Count(*)",
  t.range AS "DateRange Using DateDiff"
FROM your_table
JOIN thresholds t ON DATEDIFF(year, Start_date, End_Date) > t.min_years
GROUP BY t.range
ORDER BY t.min_years DESC;

二、关于CUBE函数的用法 #

CUBE主要用于多维度聚合（比如同时按日期区间+地区+部门统计所有组合），单维度场景下用它反而冗余。如果一定要尝试，示例如下（会额外生成总行数）：

WITH date_ranges AS (
  SELECT
    CASE
      WHEN DATEDIFF(year, Start_date, End_Date) > 50 THEN '> 50 yrs'
      WHEN DATEDIFF(year, Start_date, End_Date) > 20 THEN '> 20 yrs'
      WHEN DATEDIFF(year, Start_date, End_Date) > 10 THEN '> 10 yrs'
      ELSE '<= 10 yrs'
    END AS range
  FROM your_table
)
SELECT
  COUNT(*) AS "Count(*)",
  CASE WHEN GROUPING_ID(range) = 1 THEN 'Total' ELSE range END AS "DateRange Using DateDiff"
FROM date_ranges
GROUP BY CUBE(range)
ORDER BY GROUPING_ID(range), range DESC;

性能优化建议 #

1. 为Start_date和End_Date创建联合索引，或者创建基于DATEDIFF(year, Start_date, End_Date)的表达式索引（不同数据库语法不同，比如PostgreSQL的CREATE INDEX idx_diff ON your_table (DATEDIFF(year, Start_date, End_Date))）。
2. 如果OLTP库不允许长查询，可考虑将统计结果写入临时表或物化视图，避免影响业务。

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