如何在配置单元中创建透视/转置表

shyt4zoc  于 2021-06-27  发布在  Hive
关注(0)|答案(1)|浏览(627)

从表中选择st\ U date、st\ U symbol、st\ U adjclose;

2019-02-19  MSFT    107.71
2019-02-20  MSFT    107.15
2019-03-01  AAPL    174.97
2019-01-02  AAPL    157.25
2019-01-08  AMZN    1656.58
2019-01-07  AMZN    1629.51
2019-01-03  GOOGL   1025.47
2019-01-04  GOOGL   1078.07

date            MSFT      AAPL     AMZN      GOOGL   

2019-02-19      107.71    141.58   141.58    141.58
2019-02-20      107.71    157.25   157.25    157.25
2019-02-22      110.97    157.25   157.25    157.25
qv7cva1a

qv7cva1a1#

如果您事先知道要在查询中使用哪些股票符号,那么可以使用 MAP :

SELECT   st_date, 
         SUM(t.value_map['MSFT'])  AS MSFT, 
         SUM(t.value_map['AAPL'])  AS AAPL, 
         SUM(t.value_map['AMZN'])  AS AMZN, 
         SUM(t.value_map['GOOGL']) AS GOOGL 
FROM     ( 
                SELECT st_date, 
                       map(st_symbol,st_adjclose) AS value_map 
                FROM   ( 
                              SELECT st_date, 
                                     st_symbol, 
                                     st_adjclose 
                              FROM   TABLE ) x ) t 
GROUP BY st_date;

让我知道它是否有效

相关问题