sql - BigQuery 中的 NTILE() 用于非统一存储桶

Question

我正在尝试对 BigQuery 上的 Google Merchandise Store 示例数据集执行 RFM 分段。在我的 SQL 查询中，NTILE(5) 根据行顺序将行划分为 5 个存储桶，并返回分配给每行的存储桶编号。在这种情况下，每个桶的大小相等。想了解如何创建不同大小的桶。例如，bucket 1 包含底部的 10%，bucket 2 包含接下来的 20% 的记录等等。谢谢!

#standard SQL    
  SELECT
      fullVisitorId,
      NTILE(5) OVER (ORDER BY last_order_date) AS rfm_recency,
      NTILE(5) OVER (ORDER BY count_order) AS rfm_frequency,
      NTILE(5) OVER (ORDER BY avg_amount) AS rfm_monetary
    FROM (
      SELECT
        fullVisitorId,
        MAX(date) AS last_order_date,
        COUNT(*) AS count_order,
        AVG(totals.totalTransactionRevenue)/1000000 AS avg_amount
      FROM
        `bigquery-public-data.google_analytics_sample.ga_sessions_20170*`
      WHERE
        _table_suffix BETWEEN "101"
        AND "801"
        AND totals.totalTransactionRevenue IS NOT NULL
      GROUP BY
        fullVisitorId )

Answer 1

您可以使用 row_number()和 count(*)定义您自己的存储桶:

SELECT fullVisitorId,
       (CASE WHEN seqnum_r <= 0.1 * cnt THEN 1
             WHEN seqnum_r <= 0.3 * cnt THEN 2
             ELSE 3
        END) as bin_r,
       . . .               
FROM (SELECT fullVisitorId,
             MAX(date) AS last_order_date,
             COUNT(*) AS count_order,
             (AVG(totals.totalTransactionRevenue) / 1000000) AS avg_amount,
             COUNT(*) OVER () as cnt,
             ROW_NUMBER() OVER (ORDER BY MAX(date)) as seqnum_r,
             ROW_NUMBER() OVER (ORDER BY COUNT(*)) as seqnum_f,
             ROW_NUMBER() OVER (ORDER BY AVG(totals.totalTransactionRevenue)) as seqnum_m
      FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170*`
      WHERE _table_suffix BETWEEN "101" AND "801" AND
            totals.totalTransactionRevenue IS NOT NULL
      GROUP BY fullVisitorId
     ) rfm