sql - 交叉销售矩阵 - 在雪花中
问题描述
我正在尝试构建一个交叉销售矩阵,其结构如下所示,其中 X 是其他产品在篮子中的频率百分比:
之后我需要在 excel 或其他工具中旋转这些数据,所以我假设 Snowflake 中的查询需要输出准备好旋转的表格数据集,我正在努力解决它的逻辑。
这是我到目前为止所拥有的:
SELECT FCT.TRANSACTION_ID,
PRD.PRODUCT_TYPE,
COUNT(DISTINCT FCT.PRODUCT_ID),
COUNT(DISTINCT FCT1.PRODUCT_ID)
FROM TRANSACTION_ORDERS FCT
INNER JOIN DIM_PRODUCT PRD ON FCT.PRODUCT_ID = PRD.PRODUCT_ID
LEFT JOIN FACT_TRANSACTION_ORDERS FCT1 ON FCT.TRANSACTION_ID = FCT1.TRANSACTION_ID
AND FCT.PRODUCT_ID != FCT1.PRODUCT_ID
GROUP BY FCT.TRANSACTION_ID, FCT.PRODUCT_ID, FCT1.PRODUCT_ID
加入是否正确?或者我应该做一个交叉连接?此外,如何捕获同一篮子中两种产品的百分比频率?
非常感谢!
编辑:我试图捕捉不同产品类型出现在同一个篮子中的频率。
两个方向的组合的值相同。ProductType1 与列 ProductType2 的交集与列 Product Type1 行 ProductType2 的值相同。
在篮子交叉分析中,它们应该有所不同。每个方向都不一样。换句话说,具有ProductType1 的篮子可能有 X% 的时间有 ProductType2,但具有 ProductType2 的篮子应该有 Y%的时间有 ProductType1。
解决方案
你想要一个自我加入。我希望产品的顺序相同,但您似乎使用的是相同的交易。无论如何,这是查询的结构:
WITH TP AS (
SELECT T.*, P.PRODUCT_TYPE
FROM TRANSACTION_ORDERS T JOIN
DIM_PRODUCT P
ON T.PRODUCT_ID = P.PRODUCT_ID
)
SELECT TP.PRODUCT_TYPE, TP2.PRODUCT_TYPE,
COUNT(DISTINCT TP.TRANSACTION_ID) as NUM_ORDERS
FROM TP JOIN
TP TP2
ON TP2.TRANSACTION_ID = TP.TRANSACTION_ID
GROUP BY TP.PRODUCT_TYPE, TP2.PRODUCT_TYPE;
如果这是每个订单,您只需更改ON
外部查询中的子句以使用订单 ID。
请注意,这使用COUNT(DISTINCT)
而不是COUNT(*)
因为交易/订单可能有多个相同类型的产品。大概,您希望只计算一次。
编辑:
如果您想除以具有任一产品类型的交易数量(这对我来说很有意义),那么我会将其处理为:
WITH TP AS (
SELECT DISTINCT T.TRANSACTION_ID, P.PRODUCT_TYPE
FROM TRANSACTION_ORDERS T JOIN
DIM_PRODUCT P
ON T.PRODUCT_ID = P.PRODUCT_ID
)
SELECT TP.PRODUCT_TYPE, TP2.PRODUCT_TYPE,
COUNT(*) as NUM_ORDERS,
( MAX(CASE WHEN TP.PRODUCT_TYPE = TP2.PRODUCT_TYPE THEN COUNT(*) END) OVER (PARTITION BY TP.PRODUCT_TYPE) +
MAX(CASE WHEN TP.PRODUCT_TYPE = TP2.PRODUCT_TYPE THEN COUNT(*) END) OVER (PARTITION BY TP2.PRODUCT_TYPE) -
COUNT(*)
) as Num_Orders_Either,
( COUNT(*) * 1.0 /
( MAX(CASE WHEN TP.PRODUCT_TYPE = TP2.PRODUCT_TYPE THEN COUNT(*) END) OVER (PARTITION BY TP.PRODUCT_TYPE) +
MAX(CASE WHEN TP.PRODUCT_TYPE = TP2.PRODUCT_TYPE THEN COUNT(*) END) OVER (PARTITION BY TP2.PRODUCT_TYPE) -
COUNT(*)
) as ratio
FROM TP JOIN
TP TP2
ON TP2.TRANSACTION_ID = TP.TRANSACTION_ID
GROUP BY TP.PRODUCT_TYPE, TP2.PRODUCT_TYPE;
这将使用任一产品的订单总和减去两者的数量来计算包含这两种产品的总订单。
推荐阅读
- php - 如何在 php 中从窗口的密钥库中加载证书?
- azure - 根据更新的数据集自动重新训练分类模型
- jquery-ui - GAS PropertiesService 保存和返回排序顺序
- asp.net - 通过链接或按钮将值从 ASPX 页面传递到代码后面
- javascript - JSON.parse() 返回 [object Object] (Node.Js)
- javascript - 出现错误:预检响应中的 Access-Control-Allow-Headers
- delphi - 是否有可能找到谁建立了一个 exe 文件?
- java - 我们是否应该像在 .Net 中那样在 java 中创建 Java 源代码和 Junit 测试作为不同的项目?
- php - 使用来自 github 的 laravel 运行克隆 PHP 项目时出错
- python - 如何使用自定义域将 HTTPS 流量强制到灵活的 Google App Engine?