sql - 9 月 1 日在 8 月 31 日之前在 Zeppelin 中按日期排序的条形图，请问如何解决？

Question

在 Zeppelin 中，我有一个简单的值（Y 轴）与日期（X 轴）的条形图，它在本月（今天）的新开始之前工作正常，当时它把“9 月 1 日”放在“8 月 31 日”之前。我按日期字符串排序（因为这是我需要在图表上显示的字符串）。

询问：

%impala
SELECT FROM_TIMESTAMP(DATE_TRUNC('HOUR', concat(replace(my_timestamp,'"',''), "Z")), 'd MMM HH:mm') AS hours, COUNT(my_number) AS "number per hour"
FROM my_table
WHERE unix_timestamp(my_timestamp) > (unix_timestamp(now()) - 86400)
GROUP BY 1
ORDER BY 1 ASC
LIMIT 24;

我意识到这个问题是由于日期字符串的字母数字比较造成的。我想我可以通过为日期的 unix_timestamp() 添加第三列然后按此排序来修复它，但这会产生分组错误：

java.sql.SQLException: [Cloudera][ImpalaJDBCDriver](500051) ERROR processing query/statement. Error Code: 0, SQL state: TStatus(statusCode:ERROR_STATUS, sqlState:HY000, errorMessage:AnalysisException: select list expression not produced by aggregation output (missing from GROUP BY clause?): unix_timestamp(my_timestamp)

对于这个查询：

%impala
SELECT FROM_TIMESTAMP(DATE_TRUNC('HOUR', concat(replace(my_timestamp,'"',''), "Z")), 'd MMM HH:mm') AS hours, COUNT(my_number) AS "number per hour", unix_timestamp(my_timestamp)
FROM my_table
WHERE unix_timestamp(my_timestamp) > (unix_timestamp(now()) - 86400)
GROUP BY 1
ORDER BY 3 ASC
LIMIT 24;

如何解决请按正确顺序获取图表？

标签： sqldatetimehiveimpalaapache-zeppelin

Answer 1

以格式计算附加列yyyy-MM-dd HH:mm（与小时相同的粒度但以可排序格式）并将其添加到 groupby （在小时列之前）和 order by （而不是小时列）：

SELECT FROM_TIMESTAMP(DATE_TRUNC('HOUR', concat(replace(my_timestamp,'"',''), "Z")), 'd MMM HH:mm') AS hours, 
       FROM_TIMESTAMP(DATE_TRUNC('HOUR', concat(replace(my_timestamp,'"',''), "Z")), 'yyyy-MM-dd HH:mm') as dt,
       COUNT(my_number) AS "number per hour"
FROM my_table
WHERE unix_timestamp(my_timestamp) --also it seems Z should be removed, etc 
      > (unix_timestamp(now()) - 86400)
GROUP BY dt, hours
ORDER BY dt
LIMIT 24;