根据销售数据计算表现最佳的两个产品类别 [英] Calculate top two performing product categories from Sales data

问题描述

我正在尝试为每个客户建立性能最高的2个产品类别的KPI.我有以下相关列的销售数据-客户ID，产品，产品类别，订单数量，产品amt，订单日期

I am trying to build a KPI of top 2 performing product categories for each customer. I have sales data with following relevant columns - customerid, product, product_category, order_qty, product_amt , order_date

我在BQ中使用旧式SQL语法.

I am using legacy SQL syntax in BQ.

推荐答案

这是可能的解决方案...

This is a possible solution...

SELECT
  customer_id,
  product_category,
  order_qty
FROM (
  SELECT
    customerid, 
    product_category, 
    SUM(order_qty) AS order_qty,
    ROW_NUMBER() OVER(PARTITION BY customerid ORDER BY order_qty DESC) AS rn
  FROM
    [project:dataset.table] 
  GROUP BY
    1, 2
)
WHERE
  rn <= 2
ORDER BY
  1, 3 DESC

这篇关于根据销售数据计算表现最佳的两个产品类别的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！