如何将新列添加到结果表中？

问题描述：

这是表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，其列表["2017-06-03","2017-06-04"]的thedate的结果。当thedate属于["2017-06-05","2017-06-06"]时，它必须具有值B。 thedate中未包括在这两个列表中的其余值应分配值C。

的结果表应具有以下栏目：

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

答

您只能将它添加到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);

我可以在“When”条款中添加周末/劳动节的支票吗？是否有任何功能可以做到这一点（没有通过2017年所有周末日期的详尽列表）。如果是周末，我想把'D'放在一边，如果这是一个劳动节，那么我想把'E'。非常感谢。 – Dinosaurius

我得到的错误'无法解决'grp'给定输入列：方向' – Dinosaurius

用'CASE'语句替换'GROUP BY'中的'grp'。 – Eric

答

使用CASE语句

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

...

如何将新列添加到结果表中？

相关推荐