sql - Impala SQL 基于行数据构建列并使用额外的行数据填充列

Question

我在 Impala 工作，虽然我对 Impala 和 SQL 都缺乏经验，但我需要能够构建如下所示的数据集:

|dayname  | 2017-11-08 00:00:00 | 2017-11-08 01:00:00 | ... |
|---------|---------------------+---------------------+-----|
|Wednesday| 20                  | 11                  | ... |
|---------|---------------------|---------------------|-----|
|Thursday | 287                 | 17                  | ... |
|---------|---------------------|---------------------|-----|
|...      | ...                 | ...                 | ... |
|---------|---------------------|---------------------|-----|

由于 Impala 的限制，我无法使用 pivot，这在正常情况下会产生预期的结果。

到目前为止，我有一个 SQL SELECT 语句，如下所示:

select 
dayname(date) as dayname,
utc_hour, 
sum(case when (`type` IN ('Awesome')) then 1 else 0 end) as some
FROM (select *, trunc(cast(floor(date / 1000) as timestamp), "HH") as utc_hour
FROM COOLNESSTYPES
WHERE date >= 1510082633596 and month >= '2017-11'
)  a 
GROUP BY utc_hour, dayname
ORDER BY utc_hour;

并返回以下数据:

+-----------+---------------------+-------+
| dayname   | utc_hour            | some  |
+-----------+---------------------+-------+
| Wednesday | 2017-11-08 00:00:00 | 20    |
| Wednesday | 2017-11-08 01:00:00 | 11    |
| Wednesday | 2017-11-08 09:00:00 | 1     |
| Wednesday | 2017-11-08 11:00:00 | 40    |
| Wednesday | 2017-11-08 12:00:00 | 0     |
| Wednesday | 2017-11-08 13:00:00 | 6     |
| Wednesday | 2017-11-08 14:00:00 | 0     |
| Wednesday | 2017-11-08 16:00:00 | 2     |
| Wednesday | 2017-11-08 17:00:00 | 10    |
| Wednesday | 2017-11-08 19:00:00 | 5     |
| Thursday  | 2017-11-09 07:00:00 | 1     |
| Thursday  | 2017-11-09 12:00:00 | 0     |
| Thursday  | 2017-11-09 13:00:00 | 0     |
| Thursday  | 2017-11-09 14:00:00 | 58    |
| Friday    | 2017-11-10 09:00:00 | 0     |
| Friday    | 2017-11-10 10:00:00 | 0     |
| Friday    | 2017-11-10 16:00:00 | 0     |
+-----------+---------------------+-------+

那么，我该如何着手做这样的事情呢？在 Cloudera 的社区页面上，有人建议使用联合，但我不太清楚如何将我的列标记为 utc_hour 列中的行值。 (如果需要，请参阅 https://community.cloudera.com/t5/Interactive-Short-cycle-SQL/Transpose-columns-to-rows/td-p/49667 以获取有关联合建议的更多信息。)

如有任何帮助或想法，我们将不胜感激。谢谢!

Answer 1

如果您确实需要更改列名，则会增加复杂性。如果您可以容忍固定的列名，则数据透视表很简单，按照以下几行:

select
      dayname 
    , extract(dow from utc_hour) d_of_w
    , max(case when date_part('day', utc_hour) = 0  then somecol end) hour_0
    , max(case when date_part('day', utc_hour) = 7  then somecol end) hour_7
    , max(case when date_part('day', utc_hour) = 9  then somecol end) hour_9
    , max(case when date_part('day', utc_hour) = 12 then somecol end) hour_12
    , max(case when date_part('day', utc_hour) = 14 then somecol end) hour_14
from COOLNESSTYPES
group by
      d_of_w
    , dayname 

我使用 Postgres 为这个例子开发我的例子，使用 extract(hour from utc_hour) 而不是上面显示的 date_part()(感谢 hbomb)。

|   dayname | d_of_w | hour_0 | hour_7 | hour_9 | hour_12 | hour_14 |
|-----------|--------|--------|--------|--------|---------|---------|
| Wednesday |      3 |     20 | (null) |      1 |       0 |       0 |
|    Friday |      5 | (null) | (null) |      0 |  (null) |  (null) |
|  Thursday |      4 | (null) |      1 | (null) |       0 |      58 |

参见:http://sqlfiddle.com/#!17/81cfd/2 (Postgres)

要实现更改的列名，您需要“动态 sql”，坦率地说，目前尚不清楚这在 Impala 中是否可行(因为我不使用该产品)。