枢轴在雅典娜 [英] Pivot in aws athena

问题描述

我有一张桌子，如下所示，

I have a table as follwos,

uname   date        value
fb      2019-09-13  10
uk      2019-09-13  20
fb      2019-09-14  30
us      2019-09-13  40
us      2019-09-14  50

输出应如下所示

unmae   2019-09-13  2019-09-14
fb      40          0
us      40          50
uk      20          0

 select queue_name,kv1('2019-06-03') AS A_v1 from 
(
 select queue_name,map_agg(wb,components_processed) as kv1
from hr_flash_fd_reviewer_policy group by 1
   )

推荐答案

您可以使用条件聚合.请注意，这要求您事先知道日期列的可能值.

You can use conditional aggregation. Please note that this requires that you know in advance the possible values for the date column.

SELECT 
    uname,
    SUM(CASE WHEN mydate = date '2019-09-13' THEN value ELSE 0 END) AS "2019-09-13",
    SUM(CASE WHEN mydate = date '2019-09-14' THEN value ELSE 0 END) AS "2019-09-14"
GROUP BY uname
ORDER BY uname

注意:date对于列名来说确实是一个糟糕的选择，因为它与保留关键字冲突.我在查询中将其重命名为mydate.

NB: date is a really bad choice for a column name, since it clashes with a reserved keyword. I renamed it to mydate in the query.

这篇关于枢轴在雅典娜的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！