前文我们介绍了如何通过 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 语句实现同样功能。
本文参考链接：https://blog.csdn.net/neweastsun/article/details/118863161
