PostgreSQL 使用简单 case 实现交叉表

前文我们介绍了如何通过 crosstab 扩展实现交叉表，本文回归原始基本的SQL实现方式，利用case语句 实现交叉表，读者可以对比两者之间差异，增强SQL分析实现能力。

准备示例数据

为了演示方便，创建关于浏览web页面的记录表，包括三个字段：日期、操作系统、以及访问次数。下面填充一些随机数据：

create table daily_browse as
    select on_date::date,
           b.desc AS TYPE,
           (random() * 10000 + 1)::int AS val
     from generate_series((now() - '100 days'::interval)::date,
                          now()::date,
                          '1 day'::interval) as t(on_date),
          (SELECT unnest(ARRAY['OSX', 'Windows', 'Linux']) AS DESC) b;

select * from daily_browse limit 10;

返回示例数据：

on_date	type	val
2021-04-08	OSX	9350
2021-04-08	Windows	5812
2021-04-08	Linux	2713
2021-04-09	OSX	1657
2021-04-09	Windows	8409
2021-04-09	Linux	3656
2021-04-10	OSX	1506
2021-04-10	Windows	8321
2021-04-10	Linux	9827
2021-04-11	OSX	2355

使用case实现分组统计

上面已经准备了演示数据，现在统计每天的统计量，同时分别统计每类操作系统的浏览量：

select on_date,
          sum(case when type = 'OSX' then val end) as osx,
          sum(case when type = 'Windows' then val end) as windows,
          sum(case when type = 'Linux' then val end) as linux,
          sum(val) as subtotal
from daily_browse
group by on_date
order by on_date
limit 10;

on_date	osx	windows	linux	subtotal
2021-04-08	9350	5812	2713	17875
2021-04-09	1657	8409	3656	13722
2021-04-10	1506	8321	9827	19654
2021-04-11	2355	4205	8916	15476
2021-04-12	3045	9738	7464	20247
2021-04-13	4165	3806	8049	16020
2021-04-14	1732	6632	1707	10071
2021-04-15	7685	1473	2064	11222
2021-04-16	9522	2194	7081	18797
2021-04-17	4124	4630	1144	9898

使用filter实现分组统计

除了标准语法，PostgreSQL 9.4 提供了 filter 语法可以更简单实现同样功能：

select on_date,
          sum(val) filter(where type = 'OSX') as osx,
          sum(val) filter(where type = 'Windows') as windows,
          sum(val) filter(where type = 'Linux') as linux,
          sum(val) as subtotal
from daily_browse
group by on_date
order by on_date
limit 10;

返回结果一致：

on_date	osx	windows	linux	subtotal
2021-04-08	9350	5812	2713	17875
2021-04-09	1657	8409	3656	13722
2021-04-10	1506	8321	9827	19654
2021-04-11	2355	4205	8916	15476
2021-04-12	3045	9738	7464	20247
2021-04-13	4165	3806	8049	16020
2021-04-14	1732	6632	1707	10071
2021-04-15	7685	1473	2064	11222
2021-04-16	9522	2194	7081	18797
2021-04-17	4124	4630	1144	9898

总结

本文介绍了 使用 简单 case 语句实现交叉表查询，也可以通过filter 语句实现同样功能。