CROSSTAB 的使用

8 篇文章 0 订阅

使用CROSSTAB实现表的行列翻转,数据的统计

表1: products

id  name
1   Synergistic Marble Gloves
2   Gorgeous Paper Table
3   Awesome Wool Gloves
4   Ergonomic Leather Clock
5   Heavy Duty Iron Knife
6   Heavy Duty Paper Shirt
7   Fantastic Aluminum Table
8   Rustic Paper Plate
9   Rustic Leather Keyboard
10  Intelligent Leather Clock

表2:details

id  product_id  detail
1   10  bad
2   1   bad
3   9   ok
4   4   good
5   2   good
6   5   bad
7   7   bad
8   1   ok
9   8   good
10  8   ok
11  10  ok
12  6   bad
13  7   good
14  8   ok
15  6   bad
16  2   bad
17  9   bad
18  7   good
19  9   ok

sql:

SELECT name, good, ok, bad
FROM crosstab('
  SELECT p.name, d.detail, COUNT(1)
  FROM details AS d JOIN products AS p
    ON d.product_id = p.id
  GROUP BY p.name, d.detail
  ORDER BY p.name, d.detail
  ')
AS ct(name text, bad bigint, good bigint, ok bigint);

结果:

name                                    good    ok      bad
Aerodynamic Concrete Hat                 39     32      31
Awesome Leather Pants                    31     31      29
Durable Granite Computer                 25     35      37
Durable Linen Knife                      45     37      29
Enormous Wooden Plate                    27     38      43
Incredible Bronze Bench                  37     29      47
Mediocre Copper Keyboard                 25     37      26
Mediocre Granite Bag                     33     35      36
Rustic Steel Watch                       24     44      26
Small Granite Lamp                       25     36      31

https://www.postgresql.org/docs/9.5/static/tablefunc.html

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值