Skip to main content
 首页 » 数据库

PostgreSQL 使用简单 case 实现交叉表

2022年07月19日138lexus

前文我们介绍了如何通过 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
阅读延展