使用谷歌表格 - “查询功能”查找一天中instance_id百分比最低的时间

Using google sheets - "Query function" to find during what time of the day the instance_id percentage is lowest

提问人:Gracia M 提问时间:9/12/2023 最后编辑:Gracia M 更新时间:9/12/2023 访问量:46

问:

假设存在大约 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'")

desired outcome

但是,我收到以下错误:

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" ... "," ... "*" ... "+" ... "-" ... "/" ... "%" ... "*" ... "/" ... "%" ... "+" ... "-" ...
sql datetime google-sheets 案例 sql-timestamp

评论

0赞 Logan 9/12/2023
您是否可以共享虚拟数据供我们复制?我们无法对您当前拥有的数据进行可视化,也无法为您提供解决方法。也许把它放在另一张纸上,如果它是敏感数据,它不一定完全是你的数据,这只是为了让我们可以可视化和复制你所拥有的。最小可重复的例子。此外,如果您可以共享所需的输出

答: 暂无答案