时间戳

首页 > 解决方案 > SQLite 枢轴产生备用 NULLS

sqlite - SQLite 枢轴产生备用 NULLS

问题描述

我的 SQLite 数据透视代码有问题，主要取自 McPeppr 的回答：Pivot in SQLite

创建我的临时表：

WITH t1 AS (
SELECT      band, 
    p.name, 
    status, 
    strftime('%Y-%m', time_start) AS Month,
    AVG(time) AS Avg
FROM person p

JOIN action a ON p.person_id = a.person_id
JOIN log l ON p.log_id = l.log_id

WHERE p.person = 'Joe' AND opps = '2'

GROUP BY band, Month, status, strftime('%Y-%m', time_stamp_start)

ORDER BY Month, CASE status
                WHEN 'one' THEN 0
                WHEN 'two' THEN 1
                WHEN 'three' THEN 2
                WHEN 'four' THEN 3
            END
),

t1 看起来像：

  band |  name  | status |   month   |     AVG 
 ------+--------+--------+-----------+---------------
    1  |  Joe   |  one   |  2018-01  |     3.33      
    2  |  Joe   |  one   |  2018-01  |     4.11
    1  |  Joe   |  two   |  2018-02  |     2.55      
    2  |  Joe   |  two   |  2018-02  |     3.45 
  ..........

当我在选择中尝试枢轴时，我得到：

Select band, Month,
case when status = 'one' then response_avg end as One,
case when status = 'two' then response_avg end as Two,
...,
from t1

这个：

  band |   month    |  One  |  Two  
 ------+------------+-------+---------
    1  |  2018-01   |  3.41 |  NULL    
    2  |  2018-01   |  3.55 |  NULL
    1  |  2018-01   |  NULL |  2.55     
    2  |  2018-01   |  NULL |  4.61
    1  |  2018-02   |  1.55 |  NULL    
    2  |  2018-02   |  2.43 |  NULL
    1  |  2018-02   |  NULL |  4.33     
    2  |  2018-02   |  NULL |  3.44

而我想要

  band |   month    |  One  |  Two  
 ------+------------+-------+---------
    1  |  2018-01   |  3.41 |  2.55    
    2  |  2018-01   |  3.55 |  4.61
    1  |  2018-02   |  1.55 |  2.55     
    2  |  2018-02   |  2.43 |  4.61

我了解状态列导致此问题，但无法弄清楚如何解决它。

我已经尝试了一些很好的方法（多个临时表，子选择以删除由于默认分组而导致的“状态”），从我在这里找到的不同问题中得到了相同的结果。任何帮助表示赞赏

标签： sqlitepivot

解决方案

使用 CASE/WHEN 时的技巧是使用 MAX 之类的聚合函数，然后按所有非聚合列分组：

SELECT 
   band,
   Month,
   MAX(CASE 
          when status = 'one' then response_avg
      END) as One,
   MAX(CASE 
          when status = 'two' then response_avg
      END) as Two
FROM t1
GROUP BY band,
   Month

推荐阅读