提问人:Gracia M 提问时间:9/12/2023 最后编辑:Gracia M 更新时间:9/12/2023 访问量:46
使用谷歌表格 - “查询功能”查找一天中instance_id百分比最低的时间
Using google sheets - "Query function" to find during what time of the day the instance_id percentage is lowest
问:
假设存在大约 60 天的安全相关事件日志以及instance_id。现在,在某些情况下,事件被报告,但incident_id没有。这应该只能通过 Google 表格中的 SQL(查询函数)完成,而不是任何其他工具。
您的目标是找出一天中哪个时间incident_id百分比较低?
我尝试使用此查询来获得以下结果:
=QUERY(instances, "SELECT
CASE
WHEN TIME(C) >= TIME '06:00:00' AND TIME(C) <= TIME '12:00:00' THEN 'Morning'
WHEN TIME(C) > TIME '12:00:00' AND TIME(C) <= TIME '18:00:00' THEN 'Afternoon'
WHEN TIME(C) > TIME '18:00:00' AND TIME(C) <= TIME '23:59:59' THEN 'Evening'
ELSE 'Night'
END, COUNT(D), (COUNT(D)/COUNT(C))*100
WHERE TODATE(C) = DATE '2022-04-11'
GROUP BY
CASE
WHEN TIME(C) >= TIME '06:00:00' AND TIME(C) <= TIME '12:00:00' THEN 'Morning'
WHEN TIME(C) > TIME '12:00:00' AND TIME(C) <= TIME '18:00:00' THEN 'Afternoon'
WHEN TIME(C) > TIME '18:00:00' AND TIME(C) <= TIME '23:59:59' THEN 'Evening'
ELSE 'Night'
END
ORDER BY COUNT(D) ASC
LABEL
CASE
WHEN TIME(C) >= TIME '06:00:00' AND TIME(C) <= TIME '12:00:00' THEN 'Morning'
WHEN TIME(C) > TIME '12:00:00' AND TIME(C) <= TIME '18:00:00' THEN 'Afternoon'
WHEN TIME(C) > TIME '18:00:00' AND TIME(C) <= TIME '23:59:59' THEN 'Evening'
ELSE 'Night'
END 'time_of_day', COUNT(D) 'instances', (COUNT(D)/COUNT(C))*100 'percentage'")
但是,我收到以下错误:
Unable to parse query string for Function QUERY parameter 2: PARSE_ERROR: Encountered " <ID> "WHEN "" at line 3, column 5. Was expecting one of: <EOF> "where" ... "group" ... "pivot" ... "order" ... "skipping" ... "limit" ... "offset" ... "label" ... "format" ... "options" ... "," ... "*" ... "+" ... "-" ... "/" ... "%" ... "*" ... "/" ... "%" ... "+" ... "-" ...
答: 暂无答案
评论