代码之家 › 专栏 › 技术社区 › S31

连续日期的平均时差

google-bigquery sql

S31 · 技术社区 · 6 年前

    UserId  Login                      
    0       2016-07-14 14:43:30 +0000   
    2       2018-07-09 17:19:55 +0000   
    2       2018-07-10 20:38:37 +0000   
    2       2014-07-12 17:37:20 +0000
    2       2018-07-13 01:08:20 +0000   
    3       2015-12-04 17:17:41 +0000   
    3       2015-12-04 20:14:43 +0000

所以在这一点上我有:

  SELECT UserId
        ,CASE WHEN(
          (Magic)
          THEN AVG(TIMESTAMP_DIFF(Login2, Login, HOUR) ELSE NULL END))) AS hours
  FROM TABLE
  GROUP BY 1

(Magic)部分是我想指定每个用户的日期是否连续的卡住部分,然后取最新登录日期的差值并用当前登录日期减去它。我可以用一个延迟来表示,如果下一个日期和第一个日期之间的差值在一天之内,那么这是连续的吗?或者有没有分区/窗口函数的方法?

Login2将是下一个连续日期的第二个登录时间戳。

UserId      Average Hours between Consecutive Logins                      
    0       NULL 
    2       17.5 hours
    3       3 hours

对于用户标识0,它为空,因为没有第二次登录时间可与之比较。
对于userid2,它得到两个连续登录日期(07/09-07/10和07/12-07/13)之间的平均值(27小时+8小时/2)=5.5小时
对于userid3,一个数据点在同一天登录时有3个小时的差异,因此它符合连续条件,并得到一个数据点的平均差异

1 回复 | 直到 6 年前

Mikhail Berlyant 6 年前

下面是BigQuery标准SQL的示例

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 0 UserId, TIMESTAMP '2016-07-14 14:43:30' Login UNION ALL   
  SELECT 2, '2018-07-09 17:19:55' UNION ALL   
  SELECT 2, '2018-07-10 20:38:37' UNION ALL   
  SELECT 2, '2018-07-12 17:37:20' UNION ALL
  SELECT 2, '2018-07-13 01:08:20' UNION ALL   
  SELECT 3, '2015-12-04 17:17:41' UNION ALL   
  SELECT 3, '2015-12-04 20:14:43' 
)
SELECT
  UserId,
  ROUND(AVG(IF(
      TIMESTAMP_DIFF(TIMESTAMP_TRUNC(next_Login, DAY), TIMESTAMP_TRUNC(Login, DAY), DAY) < 2,
      TIMESTAMP_DIFF(next_Login, Login, MINUTE), NULL
  ))/60, 2) average_hours
FROM (
  SELECT UserId, Login, 
    LEAD(Login) OVER(PARTITION BY UserId ORDER BY Login) next_Login
  FROM `project.dataset.table`
)
GROUP BY UserId
ORDER BY UserId

结果为

Row     UserId      average_hours    
1       0           null     
2       2           17.41    
3       3           2.95

推荐文章

Johnny T · 基于当前值的SQL合并表[重复]

5 月前

Shukurullox Komiljonov · 从记录中获得相互和解。使用SQL

5 月前

John D · 需要为NULL或NOT NULL的WHERE子句

6 月前

ojek · 如何对SQL结果进行分组和编号?

6 月前

senek · 如何在PL/SQL中将选择结果(列)放入数组中

6 月前

Sax · 规范化Google表格(第一步)

6 月前

BarÄ±Å UÅaklÄ± · 如何在ON冲突更新中使用CTE中的值

6 月前

Jatin · 检索卷计数的动态sql抛出错误语法错误[关闭]

6 月前

Andrus · 如何在sql中查找第二个匹配项

6 月前

Nebula Tech · 在SQL Server中搜索字符串中单词的多次出现

6 月前