SQL如何统计各用户各年份交易总笔数并按年份列展示

现有两张表students和trans，需要按要求统计每个用户的年度交易笔数。

表结构说明 #

• trans表：包含trans_id、ide两个字段，trans_id的前两位字符代表交易发生年份，例如18000391对应2018年、19008978对应2019年，样例数据如下：

trans_id|ide
190000678|1
200000453|2
180089376|1
190904988|2
183867647|1

• students表：包含id、name、gender三个字段，样例数据如下：

id|name|gender
1|John|M
2|Mary|F

需求说明 #

统计每个用户在指定年份的总交易笔数，最终输出列格式为name、2018、2019、2020，样例输出如下：

name|2018|2019|2020
John|2|1|0
Mary|0|1|1

现有错误实现 #

用户自行编写的代码存在语法和逻辑问题：

WITH transcations AS (
 SELECT 
 f.('20')||(SUBSTR(trans_id),1,2),
 f.ide,
 g.id,
 g.name,
 g.gender
 FROM 
 db.trans f 
   INNER JOIN db.students g
   ON f.ide = g.id 
 )
 SELECT *
 FROM transcations 
 PIVOT(MAX(ID) FOR CATEGORY IN (2018, 2019, 2020) ) P
 GROUP BY ID;

附建表与测试数据语句 #

-- create a table
CREATE TABLE students (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
gender TEXT NOT NULL
 );

CREATE TABLE trans (
 trans_id INTEGER,
 ide INTEGER FOREIGN KEY
 );

-- insert some values
INSERT INTO trans VALUES (1800034,1);
INSERT INTO trans VALUES (2000012,2);
INSERT INTO trans VALUES (1900092,1);
INSERT INTO trans VALUES (2000000,1);
INSERT INTO trans VALUES (1800904,1);
-- insert some values
INSERT INTO students VALUES (1,'John','M');
INSERT INTO students VALUES (2,'Mary','F');

正确实现思路与代码 #

原有代码问题梳理 #

1. 年份提取语法错误：f.('20')写法无效，SUBSTR参数格式错误，也没有给年份字段设置别名
2. 聚合逻辑错误：统计交易笔数需要用计数聚合COUNT/SUM，而非取最大值MAX(ID)
3. 关联逻辑不合理：INNER JOIN会过滤掉没有交易记录的学生，无法保证所有用户都出现在结果中
4. PIVOT语法使用错误：PIVOT本身会自动完成分组，不需要额外写GROUP BY

方法1：条件聚合（通用写法，兼容所有主流数据库） #

该写法不依赖数据库的PIVOT语法，兼容性最强：

SELECT 
  s.name,
  SUM(CASE WHEN '20' || SUBSTR(CAST(t.trans_id AS TEXT),1,2) = '2018' THEN 1 ELSE 0 END) AS "2018",
  SUM(CASE WHEN '20' || SUBSTR(CAST(t.trans_id AS TEXT),1,2) = '2019' THEN 1 ELSE 0 END) AS "2019",
  SUM(CASE WHEN '20' || SUBSTR(CAST(t.trans_id AS TEXT),1,2) = '2020' THEN 1 ELSE 0 END) AS "2020"
FROM students s
LEFT JOIN trans t ON s.id = t.ide
GROUP BY s.id, s.name
ORDER BY s.id;

方法2：PIVOT写法（适用于支持PIVOT语法的数据库：Oracle、SQL Server、Spark SQL等） #

WITH transactions AS (
  SELECT
    g.name,
    '20' || SUBSTR(CAST(f.trans_id AS TEXT),1,2) AS trade_year,
    1 AS cnt
  FROM students g
  LEFT JOIN trans f ON g.id = f.ide
)
SELECT 
  name,
  NVL("2018",0) AS "2018",
  NVL("2019",0) AS "2019",
  NVL("2020",0) AS "2020"
FROM transactions
PIVOT(
  SUM(cnt) 
  FOR trade_year IN ('2018' AS "2018", '2019' AS "2019", '2020' AS "2020")
)
ORDER BY name;

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