替代CTE简化SQL查询：按小时统计收银员交易数的优化方案

我有一张交易表，结构如下：

timestamp           | cashier   | amount |
-----------------------------------------
2023-05-01 07:15:25 | cashier_1 | 25     |
2023-05-01 08:16:25 | cashier_1 | 18     |
2023-05-01 09:35:00 | cashier_2 | 76     |
2023-05-01 10:26:00 | cashier_3 | 45     |
2023-05-01 10:42:13 | cashier_3 | 12     |
2023-05-01 11:04:12 | cashier_4 | 80     |

需要生成一份报表，展示各收银员每小时的交易数量，报表格式如下：

hour  | cashier_1 | cashier_2 | cashier_3 |
-------------------------------------------
7:00  |   43      |   14      |    98     | 
8:00  |   12      |   67      |    76     |
9:00  |   15      |   32      |    54     |
10:00 |   54      |   45      |    34     |
11:00 |   12      |   98      |    54     |
12:00 |   18      |   76      |    65     |
13:00 |   56      |   12      |    76     |

当前我使用多个CTE编写查询，代码如下：

WITH cashier_1 AS (
    SELECT HOUR(timestamp) AS HOUR,
        COUNT(*) as cashier
    FROM my_table
    WHERE cashier = 'cashier_1'
    GROUP BY HOUR(timestamp)
    ORDER BY HOUR(timestamp)
),
cashier_2 AS (
    SELECT HOUR(timestamp) AS HOUR,
        COUNT(*) as cashier
    FROM my_table
    WHERE cashier = 'cashier_2'
    GROUP BY HOUR(timestamp)
    ORDER BY HOUR(timestamp)
),
cashier_3 AS (
    SELECT HOUR(timestamp) AS HOUR,
        COUNT(*) as cashier
    FROM my_table
    WHERE cashier = 'cashier_3'
    GROUP BY HOUR(timestamp)
    ORDER BY HOUR(timestamp)
)
SELECT CONCAT(CAST(c1.HOUR AS varchar), ':00') as HOUR,
    c1.cashier AS cashier_1,
    c2.cashier AS cashier_2,
    c3.cashier AS cashier_3
FROM cashier_1 c1
    LEFT JOIN cashier_2 c2 ON c1.HOUR = c2.HOUR
    LEFT JOIN cashier_3 c3 ON c1.HOUR = c3.HOUR
ORDER BY c1.HOUR

但由于收银员数量众多，这种写法会导致查询代码长达数百行。请问是否有SQL方案可以简化查询的冗长性，从而提升代码的可维护性和扩展性？

1. 静态透视（适用于收银员列表固定的场景） #

如果收银员名单固定，用条件聚合替代多CTE，仅需一次分组即可完成统计，代码大幅精简：

SELECT 
    CONCAT(CAST(HOUR(timestamp) AS VARCHAR), ':00') AS hour,
    COUNT(CASE WHEN cashier = 'cashier_1' THEN 1 END) AS cashier_1,
    COUNT(CASE WHEN cashier = 'cashier_2' THEN 1 END) AS cashier_2,
    COUNT(CASE WHEN cashier = 'cashier_3' THEN 1 END) AS cashier_3,
    -- 新增收银员时，仅需追加对应行即可
    COUNT(CASE WHEN cashier = 'cashier_4' THEN 1 END) AS cashier_4
FROM my_table
GROUP BY HOUR(timestamp)
ORDER BY HOUR(timestamp);

若需要将无交易的小时显示为0而非NULL，可将COUNT替换为SUM并指定ELSE值：

SUM(CASE WHEN cashier = 'cashier_1' THEN 1 ELSE 0 END) AS cashier_1

2. 动态透视（适用于收银员列表不固定的场景） #

若收银员数量频繁变化，静态写法仍需手动修改代码，此时可采用动态SQL自动生成透视列，不同数据库语法略有差异：

MySQL/MariaDB 版本

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'COUNT(CASE WHEN cashier = ''',
      cashier,
      ''' THEN 1 END) AS ',
      cashier
    )
  ) INTO @sql
FROM my_table;

SET @sql = CONCAT('SELECT CONCAT(CAST(HOUR(timestamp) AS CHAR), '':00'') AS hour, ', @sql, ' 
                  FROM my_table 
                  GROUP BY HOUR(timestamp) 
                  ORDER BY HOUR(timestamp);');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SQL Server 版本

DECLARE @cols AS NVARCHAR(MAX),
        @query  AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT DISTINCT ',' + QUOTENAME(cashier) 
                   FROM my_table
                   FOR XML PATH(''), TYPE
                  ).value('.', 'NVARCHAR(MAX)'),1,1,'');

SET @query = 'SELECT CONCAT(CAST(DATEPART(HOUR, timestamp) AS VARCHAR), '':00'') AS hour, ' + @cols + ' 
              FROM (
                    SELECT cashier, DATEPART(HOUR, timestamp) AS hour
                    FROM my_table
                   ) AS src
              PIVOT (
                    COUNT(cashier)
                    FOR cashier IN (' + @cols + ')
                   ) AS pvt
              ORDER BY hour;';

EXECUTE(@query);

PostgreSQL 版本

需先启用tablefunc扩展，再使用crosstab函数实现透视：

-- 启用扩展（仅需执行一次）
CREATE EXTENSION IF NOT EXISTS tablefunc;

SELECT * FROM crosstab(
  'SELECT CONCAT(CAST(HOUR(timestamp) AS VARCHAR), '':00'') AS hour,
          cashier,
          COUNT(*) AS count
   FROM my_table
   GROUP BY HOUR(timestamp), cashier
   ORDER BY 1, 2',
  'SELECT DISTINCT cashier FROM my_table ORDER BY 1'
) AS ct(
  hour VARCHAR,
  cashier_1 INT,
  cashier_2 INT,
  cashier_3 INT,
  cashier_4 INT -- 根据实际收银员数量补充列定义
);

若需完全动态生成列，可结合PL/pgSQL编写自定义函数实现。

方案对比 #

• 静态透视：代码简洁、性能稳定，适合收银员名单固定的场景。
• 动态透视：无需手动维护列、扩展性强，适合收银员频繁新增/删除的场景，但调试复杂度较高，部分数据库需权限支持动态SQL。

内容的提问来源于stack exchange，提问作者David López