BigQuery StandardSQL按时间戳对visit_ref排名异常问题求助

Let's break down why your visitrank is always returning 1, and fix the query to get the ranking you expect.

The Core Issue in Your Original Code #

Your RANK() window function is partitioning by both the unique visit reference (visit_ref) and the timestamp—this means each partition only contains a single row (since each visit has a unique combination of these two values). That's why every row gets a rank of 1.

To rank visits per user (earliest visit = rank 1, latest = highest rank), you need to partition by the user ID instead, and order by the visit timestamp in ascending order.

Modified Query #

SELECT
  TIMESTAMP_SECONDS(visitStartTime) AS stamp,
  customDimension.value AS UserID,
  CONCAT(CAST(fullVisitorId AS STRING), CAST(visitId AS STRING)) AS visit_ref,
  -- Total visits per user in the date range
  COUNT(DISTINCT CONCAT(CAST(fullVisitorId AS STRING), CAST(visitId AS STRING))) OVER (PARTITION BY customDimension.value) AS total_visits_in_cycle,
  -- Rank visits per user: earliest = 1, latest = highest rank
  RANK() OVER (PARTITION BY customDimension.value ORDER BY TIMESTAMP_SECONDS(visitStartTime) ASC) 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
-- Deduplicate rows first to avoid counting the same visit multiple times
GROUP BY 1, 2, 3, fullVisitorId, visitId, visitStartTime, transaction.transactionid
ORDER BY UserID, visitrank
LIMIT 500

Key Changes Explained #

1. Fixed the RANK() Partition & Order:

   • Partition by customDimension.value (your UserID) so ranking is done per unique user.
   • Order by TIMESTAMP_SECONDS(visitStartTime) ASC to assign rank 1 to the earliest visit, with ranks increasing for newer visits. If you wanted the latest visit to be rank 1 instead, swap ASC to DESC.

2. Added transaction.transactionid to GROUP BY:

   • Since you're aggregating COUNT(DISTINCT transaction.transactionid), including this in the GROUP BY ensures you don't get unexpected duplicates from the UNNEST(hits) join (which can multiply rows if a visit has multiple hits).

3. Adjusted ORDER BY:

   • Added visitrank to the final ORDER BY to make it easier to verify the ranking per user.

Example Output (Matching Your Expectation) #

For a user with 3 visits, you'll see:

stamp                     UserID                                  visit_ref                          total_visits_in_cycle  visitrank  orders
2018-05-06 21:59:20.000 UTC  00008736-01f0-4e0e-8e3b-4dc398e5b6f8  74664051693279955771525643960     3                      1          0
2018-05-07 05:39:15.000 UTC  00008736-01f0-4e0e-8e3b-4dc398e5b6f8  74664051693279955771525671555     3                      2          0
2018-05-07 08:02:30.000 UTC  00008736-01f0-4e0e-8e3b-4dc398e5b6f8  74664051693279955771525680150     3                      3          0

内容的提问来源于stack exchange，提问作者Ben P