在配置单元中转置数据

7nbnzgx9 于 2021-06-28 发布在 Hive

关注(0)|答案(2)|浏览(265)

我有一个配置单元表，其中包含以下格式的数据

day       class  start_time  count  kpi1  kpi2  kpi3  kpi4  ...  kpi160
-----------------------------------------------------------------------
20161010  abc    00          12     1     0     null  0     ...

我想编写一个配置单元查询，使用如下计算以下面的格式获取数据 max , min ，和 avg .

day       class  start_time  count  kpi_name  kpi_max   kpi_min   kpi_avg
-----------------------------------------------------------------------
20161010  abc    00          12     kpi1      max(kpi1) min(kpi1) avg(kpi1)
20161010  abc    00          12     kpi2      max(kpi2) min(kpi2) avg(kpi2)

请建议一个解决方案来获取这种格式的数据。
谢谢。

Hive hiveql

来源：https://stackoverflow.com/questions/39962583/transposing-data-in-hive

2条答案

按热度按时间

wswtfjt71#

如果你想得到min，max，avg，你必须指定groupby列，假设你想按天分组。

SELECT day,
       class,
       start_time,
       count,
       kpi1,
       MAX(kpi1) as max_kpi1,
       MIN(kpi1) as min_kpi1,
       AVG(kpi1) as avg_kpi1

FROM table

GROUP BY day

赞(0）回复(0）举报 2021-06-28

h6my8fg22#

你需要把所有的 kpi 在Map中，分解Map以创建一列，然后聚合。
例子：
数据：

+---------+------+-----------+-------+-----+-----+------+------+------+------+
|day_     |class |start_time |count_ |kpi0 |kpi1 | kpi2 | kpi3 | kpi4 | kpi5 |
+---------+------+-----------+-------+-----+-----+------+------+------+------+
|20161010 |abc   |00         |12     |1    |2    |3     |8     |9     |6     |
+---------+------+-----------+-------+-----+-----+------+------+------+------+
|20161010 |abc   |00         |12     |4    |5    |null  |6     |10    |null  |
+---------+------+-----------+-------+-----+-----+------+------+------+------+

查询：

SELECT day_
  , class
  , start_time
  , count_
  , kpi_type
  , MAX(vals) AS max_vals
  , MIN(vals) AS min_vals
  , AVG(vals) AS avg_vals
FROM (
  SELECT day_, class, start_time, count_, kpi_type, vals
  FROM database.table
  LATERAL VIEW EXPLODE(MAP('kpi0', kpi0
                         , 'kpi1', kpi1
                         , 'kpi2', kpi2
                         , 'kpi3', kpi3
                         , 'kpi4', kpi4
                         , 'kpi5', kpi5)) et AS kpi_type, vals ) x
GROUP BY day_, class_, start_time, count_, kpi_type

输出：

+---------+------+-----------+-------+---------+---------+---------+---------+
|day_     |class |start_time |count_ |kpi_type |max_vals |min_vals |avg_vals |
+---------+------+-----------+-------+---------+---------+---------+---------+
|20161010 |abc   |00         |12     |kpi0     |4        |1        |2.5      | 
+---------+------+-----------+-------+---------+---------+---------+---------+
|20161010 |abc   |00         |12     |kpi1     |5        |2        |3.5      |
+---------+------+-----------+-------+---------+---------+---------+---------+
|20161010 |abc   |00         |12     |kpi2     |3        |3        |3.0      |
+---------+------+-----------+-------+---------+---------+---------+---------+
|20161010 |abc   |00         |12     |kpi3     |8        |6        |7.0      |
+---------+------+-----------+-------+---------+---------+---------+---------+
|20161010 |abc   |00         |12     |kpi4     |10       |9        |9.5      |
+---------+------+-----------+-------+---------+---------+---------+---------+
|20161010 |abc   |00         |12     |kpi5     |6        |6        |6.0      |
+---------+------+-----------+-------+---------+---------+---------+---------+

赞(0）回复(0）举报 2021-06-28

我来回答

在配置单元中转置数据

2条答案

相关问题

热门标签

最新问答