时间戳

首页 > 解决方案 > 在 StandardSQL 中按时间戳对访问进行排名

sql - 在 StandardSQL 中按时间戳对访问进行排名

问题描述

我正在建立用户与网站交互的日志，到目前为止，每次访问都有一行显示推荐渠道和时间戳：

在此处输入图像描述

我想visit_ref按日期对每个人进行排名，以便在我查询的日期范围内，最近的排名最高，最远的排名最低。

到目前为止，这是我的代码，删除了通道以使其更易于阅读：

SELECT TIMESTAMP_SECONDS(visitStartTime) AS stamp, 
customDimension.value AS UserID,
CONCAT(CAST(fullVisitorId AS STRING),CAST(visitId AS STRING)) AS visit_ref,
COUNT(DISTINCT CONCAT(CAST(fullVisitorId AS STRING),CAST(visitId AS STRING))) OVER (PARTITION BY customDimension.value) AS total_visits_in_cycle,
RANK() OVER (PARTITION BY CONCAT(CAST(fullVisitorId AS STRING),CAST(visitId AS STRING)), TIMESTAMP_SECONDS(visitStartTime) ORDER BY TIMESTAMP_SECONDS(visitStartTime)) AS visitrank,
  COUNT(DISTINCT transaction.transactionid) AS orders

FROM `xxx.xxx.ga_sessions_20*` AS t
  CROSS JOIN UNNEST(hits) AS hits
  CROSS JOIN UNNEST(t.customdimensions) AS customDimension
WHERE parse_date('%y%m%d', _table_suffix) between 
DATE_sub(current_date(), interval 3 day) and
DATE_sub(current_date(), interval 1 day)
AND customDimension.index = 2
GROUP BY 1,2,3, fullVisitorId, visitid, visitStartTime
ORDER BY UserID
LIMIT 500

在这个例子中，按排名总是回来，1如屏幕截图所示，我怎样才能visit_ref通过时间戳获得唯一的排名？

我想要的输出如下，其中针对此用户visitrank显示1了最旧的访问和3最新的访问：

2   2018-05-07 08:02:30.000 UTC 00008736-01f0-4e0e-8e3b-4dc398e5b6f8    74664051693279955771525680150   3   2   Email - CRM Campaigns   0    
3   2018-05-06 21:59:20.000 UTC 00008736-01f0-4e0e-8e3b-4dc398e5b6f8    74664051693279955771525643960   3   1   Email - CRM Campaigns   0    
4   2018-05-07 05:39:15.000 UTC 00008736-01f0-4e0e-8e3b-4dc398e5b6f8    74664051693279955771525671555   3   3   Email - CRM Campaigns   0

RANK() OVER (PARTITION BY CONCAT(CAST(fullVisitorId AS STRING),CAST(visitId AS STRING)), TIMESTAMP_SECONDS(visitStartTime) ORDER BY TIMESTAMP_SECONDS(visitStartTime)) AS visitrank,

我正在使用 Google BigQuery StandardSQL。

标签： sqlgoogle-bigquery

解决方案

分区窗口定义了应该考虑的记录子集。通过包含TIMESTAMP_SECONDS(visitStartTime)，您将分区设置为始终为 1 的记录（尽管有时您的实际数据中可能会有更多记录）并且您只会看到 1 的排名。

我也不清楚为什么你需要在分区定义中进行连接/转换，尽管在这个转换过程中可能会发生一些重要的转换。我会用这个：

rank() over (partition by fullVisitorId order by timestamp_seconds(visitStartTime) desc)

推荐阅读