如何从bigquery获得基于时间的结果？

a2mppw5e 于 2021-07-26 发布在 Java

关注(0)|答案(1)|浏览(298)

我需要获取特定时间段的数据。目前，我只能根据日期获取数据。如何获取相同日期范围但时间范围为下午3-5点的数据？

SELECT
  event_name,
  COUNT(DISTINCT user_pseudo_id) as uniques,
  COUNT(user_pseudo_id) as total
FROM
  `XYZ.analytics_123.events_*`
WHERE
  app_info.install_source = "iTunes"   
  AND (Extract (DATE FROM TIMESTAMP_MICROS( user_first_touch_timestamp )) between "2020-05-20" and 
        "2020-05-20")
  AND (_TABLE_SUFFIX between "20200520" AND "20200520")

GROUP BY
  event_name

sql google-bigquery google-analytics ios firebase

来源：https://stackoverflow.com/questions/61996568/how-to-get-results-based-on-time-from-bigquery

1条答案

按热度按时间

6ju8rftf1#

你可以用 EXTRACT(HOUR FROM TIMESTAMP_MICROS(user_first_touch_timestamp )) BETWEEN 15 AND 17 如下例所示


# standardSQL

SELECT
  event_name,
  COUNT(DISTINCT user_pseudo_id) AS uniques,
  COUNT(user_pseudo_id) AS total
FROM `XYZ.analytics_123.events_*`
WHERE app_info.install_source = "iTunes"   
  AND (EXTRACT (DATE FROM TIMESTAMP_MICROS(user_first_touch_timestamp )) BETWEEN "2020-05-20" AND "2020-05-20")
  AND (_TABLE_SUFFIX BETWEEN "20200520" AND "20200520")
  AND (EXTRACT(HOUR FROM TIMESTAMP_MICROS(user_first_touch_timestamp )) BETWEEN 15 AND 17)
GROUP BY event_name

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

我来回答

如何从bigquery获得基于时间的结果？

1条答案

相关问题

热门标签

最新问答