google-analytics - BigQuery 时间字符串，如 Google Analytics H*HH :MM:SS

Question

有人想知道如何让 BigQuery 中的这些奇怪的时间字符串看起来像 Google Analytics 中的那些？

它们由[任意小时数]:[分钟]:[秒]组成，这就是正常时间函数实际上不起作用的原因。

例子:

• 85667:34:02
• 260:59:34
• 02:01:01

Answer 1

Would be cool if anyone finds a solution with fewer functions involved and posts it here

唯一让我(总是)有点头晕的是那些CAST、FLOOR和MOD的使用

所以，下面是转换为 ga 格式的更美观(从我的角度来看)的版本

先举个简单的例子——我认为这是不言自明的

#standardSQL
WITH `table` AS (
  SELECT 1925 AS seconds  UNION ALL
  SELECT 3600             UNION ALL
  SELECT 86430            UNION ALL
  SELECT 1111111925 
)
SELECT seconds, 
  FORMAT('%02d:%s', hours, FORMAT_TIMESTAMP('%M:%S', ts)) as ga_style
FROM `table`, 
UNNEST([STRUCT(
  TIMESTAMP_SECONDS(seconds) AS ts, 
  TIMESTAMP_DIFF(TIMESTAMP_SECONDS(seconds), TIMESTAMP_SECONDS(0), HOUR) AS hours
)])

结果为

Row       seconds       ga_style     
1           1,925       00:32:05     
2           3,600       01:00:00     
3          86,430       24:00:30     
4   1,111,111,925   308642:12:05       

最后，下面是用 SQL UDF 包装并应用于与 OP 答案相同的脚本的上述逻辑

CREATE TEMP FUNCTION time_str(seconds INT64) AS ((
  SELECT FORMAT('%02d:%s', hours, FORMAT_TIMESTAMP('%M:%S', ts)) 
  FROM UNNEST([STRUCT(
    TIMESTAMP_SECONDS(seconds) AS ts, 
    TIMESTAMP_DIFF(TIMESTAMP_SECONDS(seconds), TIMESTAMP_SECONDS(0), HOUR) AS hours
  )]) 
));
SELECT
  device.browser, 
  time_str(SUM(totals.timeOnSite)) timeOnSite
FROM `google.com:analytics-bigquery.LondonCycleHelmet.ga_sessions_20130910`
GROUP BY 1
ORDER BY SUM(totals.timeOnSite) DESC 

或者进一步简化:

CREATE TEMP FUNCTION time_str(seconds INT64) AS (
  FORMAT('%02d:%s', 
    TIMESTAMP_DIFF(TIMESTAMP_SECONDS(seconds), TIMESTAMP_SECONDS(0), HOUR), 
    FORMAT_TIMESTAMP('%M:%S', TIMESTAMP_SECONDS(seconds))
  ) 
);
SELECT
  device.browser, 
  time_str(SUM(totals.timeOnSite)) timeOnSite
FROM `google.com:analytics-bigquery.LondonCycleHelmet.ga_sessions_20130910`
GROUP BY 1
ORDER BY SUM(totals.timeOnSite) DESC

结果为

Row browser          timeOnSite  
1   Chrome             00:32:05  
2   Firefox            00:12:40  
3   Android Browser    00:05:04  
4   Safari             00:03:28  
5   Internet Explorer  00:00:26    

注意:尽管我的重点主要放在美学方面——同时这个版本有 6 个功能，而原始帖子中有 8 个——如果它真的很重要的话:o)