sql - BigQuery - 按多列排名

Question

如果我写错了或者说错了，请原谅我。

我想要一个按日期和 fullvisitorid 对访问者 order_id 进行排名的排名列，如下所示。这可能吗？如果是这样，代码行会是什么样子？

日期	全访问者 ID	order_id	秩
20201127	2148490690588403919	MUK105556400	1
20201128	2148490690588403919	MUK105556401	2
20201129	2148490690588403919	MUK105606555	3
20201127	6444290669077908463	MUK105521707	1
20201128	6444290669077908463	MUK105633922	2
20201129	6444290669077908463	MUK105630521	3

SELECT
  DISTINCT date,
  fullVisitorId,
  order_id,
FROM (
  SELECT
    date,
    fullVisitorId,
    hits.TRANSACTION.transactionId AS order_id
  FROM
    `table_name`,
    UNNEST(hits) AS hits
  WHERE
    date BETWEEN '20201127'
    AND '20201130'
    AND hits.TRANSACTION.transactionId IS NOT NULL
    AND geoNetwork. country = 'United Kingdom'
    AND fullVisitorId IN ('2148490690588403919',
      '6444290669077908463'))
ORDER BY
  2 ASC,
  1 ASC

标签： sqlgoogle-analyticsgoogle-bigquery

Answer 1

请参阅下面的示例

#standardSQL
with `project.dataset.table` as (
  select '20201127' date, 2148490690588403919 fullvisitorID,'MUK105556400' order_id union all
  select '20201128', 2148490690588403919,'MUK105556401' union all
  select '20201129', 2148490690588403919,'MUK105606555' union all
  select '20201127', 6444290669077908463,'MUK105521707' union all
  select '20201128', 6444290669077908463,'MUK105633922' union all
  select '20201129', 6444290669077908463,'MUK105630521' 
)
select *,
  rank() over(partition by fullvisitorID order by date, order_id) rank
from `project.dataset.table`         

带输出