pivot.md

# 透视表

现有销售记录表

```postgresql
create table sales(
    id serial primary key ,
    sku_id integer not null ,
    amount money,
    meta jsonb default '{}'::jsonb,
    created_at timestamp default now()
);
create index on sales(created_at);
```

现在我们希望对这个表做一个月度的透视汇总，得到2020年每个月每种商品(sku_id)的的销售总额，每个月一列，哪一项可以实现？

## 答案

```postgresql
select sku_id, 
       sum(case extract(month from created_at) when 1 then amount else 0::money end) as Jan,
       sum(case extract(month from created_at) when 2 then amount else 0::money end) as Feb,
       sum(case extract(month from created_at) when 3 then amount else 0::money end) as Mar,
       sum(case extract(month from created_at) when 4 then amount else 0::money end) as Apr,
       sum(case extract(month from created_at) when 5 then amount else 0::money end) as May,
       sum(case extract(month from created_at) when 6 then amount else 0::money end) as June,
       sum(case extract(month from created_at) when 7 then amount else 0::money end) as July,
       sum(case extract(month from created_at) when 8 then amount else 0::money end) as Aug,
       sum(case extract(month from created_at) when 9 then amount else 0::money end) as Sept,
       sum(case extract(month from created_at) when 10 then amount else 0::money end) as Oct,
       sum(case extract(month from created_at) when 11 then amount else 0::money end) as Nov,
       sum(case extract(month from created_at) when 12 then amount else 0::money end) as Dec
from sales
where created_at between '2020-01-01'::timestamp and '2021-01-01'::timestamp
group by sku_id; 
```

## 选项

### 格式不相符

```postgresql
select sku_id, extract(month from created_at) as month, sum(amount) 
from sales
where created_at between '2020-01-01'::timestamp and '2021-01-01'::timestamp
group by 1, 2; 
```

### 计算逻辑错误

```postgresql
select sku_id, 
       sum(amount) as Jan,
       sum(amount) as Feb,
       sum(amount) as Mar,
       sum(amount) as Apr,
       sum(amount) as May,
       sum(amount) as June,
       sum(amount) as July,
       sum(amount) as Aug,
       sum(amount) as Sept,
       sum(amount) as Oct,
       sum(amount) as Nov,
       sum(amount) as Dec
from sales
where created_at between '2020-01-01'::timestamp and '2021-01-01'::timestamp
group by sku_id, extract(month from created_at); 
```

### 计算格式错误

```postgresql
select sku_id, 
       sum(amount having extract(month from created_at) = 1) as Jan,
       sum(amount having extract(month from created_at) = 2) as Feb,
       sum(amount having extract(month from created_at) = 3) as Mar,
       sum(amount having extract(month from created_at) = 4) as Apr,
       sum(amount having extract(month from created_at) = 5) as May,
       sum(amount having extract(month from created_at) = 6) as June,
       sum(amount having extract(month from created_at) = 7) as July,
       sum(amount having extract(month from created_at) = 8) as Aug,
       sum(amount having extract(month from created_at) = 9) as Sept,
       sum(amount having extract(month from created_at) = 10) as Oct,
       sum(amount having extract(month from created_at) = 11) as Nov,
       sum(amount having extract(month from created_at) = 12) as Dec
from sales
where created_at between '2020-01-01'::timestamp and '2021-01-01'::timestamp
group by sku_id, extract(month from created_at); 
```