sql—如何向结果表中添加新列？

am46iovg 于 2021-06-26 发布在 Hive

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

这是table mytable :

identifier  thedate            direction
111         2017-06-03 11:20   2
111         2017-06-03 12:22   1
222         2017-06-04 12:15   1
333         2017-06-05 12:21   1
444         2017-06-05 12:39   2
444         2017-06-08 14:23   2
555         2017-06-08 15:33   1
555         2017-06-08 16:12   2

我计算apache配置单元中每小时唯一标识符的平均计数，如下所示：

SELECT HOUR(thedate) as hour, 
       COUNT(DISTINCT identifier, CAST(thedate as date),
       HOUR(thedate)) / COUNT(DISTINCT CAST(thedate as date),
       HOUR(thedate)) as hourly_avg_count
FROM mytable
GROUP BY HOUR(thedate)

现在我需要向结果表中添加一个新的计算列（不是原来的列）。此列称为 newcolumn 应该有价值 A 对于 thedate 从列表中 ["2017-06-03","2017-06-04"] . 它一定有价值 B 什么时候 thedate 属于 ["2017-06-05","2017-06-06"] . 其他的价值观 thedate 未包含在两个列表中的应具有 C 分配。
结果表应具有以下列：

newcolumn    hour    hourly_avg_count
A            11      0.5
A            12      1
B            ...     ...            
C            ...     ...

sql Hive

来源：https://stackoverflow.com/questions/45337622/how-to-add-a-new-column-to-the-result-table

2条答案

按热度按时间

qv7cva1a1#

你只要把这个加到 GROUP BY :

SELECT (CASE WHEN DATE(thedate) IN ('2017-06-03', '2017-06-04') THEN 'A'
             WHEN DATE(thedate) IN ('2017-06-05', '2017-06-06') THEN 'B'
             ELSE 'C'
        END) as grp,
       HOUR(thedate) as hour, 
       COUNT(DISTINCT identifier, CAST(thedate as date), HOUR(thedate)
             ) / COUNT(DISTINCT CAST(thedate as date), HOUR(thedate)) as hourly_avg_count
FROM mytable
GROUP BY HOUR(thedate),
         (CASE WHEN DATE(thedate) IN ('2017-06-03', '2017-06-04') THEN 'A'
               WHEN DATE(thedate) IN ('2017-06-05', '2017-06-06') THEN 'B'
               ELSE 'C'
          END);

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

7tofc5zh2#

用例语句

SELECT CASE WHEN thedate BETWEEN '2017-06-03' AND '2017-06-04'
            THEN 'A'
            WHEN thedate BETWEEN '2017-06-05' AND '2017-06-06'
            THEN 'B'
            ELSE 'C'
        END newcolumn

...

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

我来回答

sql—如何向结果表中添加新列？

2条答案

相关问题

热门标签

最新问答