SQL/BigQuery-一起销售的产品列表

Question

你好吗？

我有一个销售表，上面有DATE、TICKET_ID(交易ID(和PRODUCT_ID(售出的产品(。我想有一份每天一起销售的商品清单(也就是说，今天产品X和产品Y一起卖了10次，昨天产品X和商品Y一起卖5次…(

我有这个代码，但是它有两个问题：

1-生成反向重复项。示例：

product_id    product_id_bought_with  counting
12345         98765                   130
98765         12345                   130
abcde         fghij                   88
fghij         abcde                   88

2-此代码在没有数据列的情况下运行良好。在我输入后，数据量要大得多，我得到了一个限制错误。"执行查询期间超出了资源：无法在分配的内存中执行查询。峰值使用量：限制的152%。顶级内存消耗者：ORDER BY操作：99%其他/未分配：1%">

我的代码：

SELECT
c.DATE,
c.product_id,
c.product_id_bought_with,
count(*) counting

FROM ( SELECT a.DATE, a.product_id, b.product_id as product_id_bought_with
FROM `MY-TABLE` a

INNER JOIN `THE-SAME-TABLE` b
ON a.ID_TICKETS = b.ID_TICKETS
AND a.product_id != b.product_id
AND a.DATE = b.DATE
) c
GROUP BY DATE, product_id, product_id_bought_with
ORDER BY counting DESC

我对如何做到这一点持开放态度。提前感谢！

编辑：示例

CREATE TABLE `project_id.dataset.table_name` (
DAT_VTE DATE,
ID_TICKET STRING,
product_id int
);
INSERT INTO `project_id.dataset.table_name` (DAT_VTE, ID_TICKET, product_id)
VALUES 
(DATE('2022-01-01'), '123_abc', 876123),
(DATE('2022-01-01'), '123_abc', 324324),
(DATE('2022-01-02'), '456_def', 876123),
(DATE('2022-01-02'), '456_def', 324324),
(DATE('2022-01-02'), '456_def', 432321),
(DATE('2022-05-23'), '987_xyz', 876123),
(DATE('2022-05-23'), '987_xyz', 324324)

Answer 1

对于您的需求，您可以尝试以下查询：

with mytable as(

select *,row_number()over (partition by DAT_VTE,ID_TICKET)rownum from `project_id.dataset.MY-TABLE`
)

select DAT_VTE
,product_id
,product_id_bought_with
,count(*) counting
from (
select a.DAT_VTE,a.ID_TICKET,a.product_id as product_id, b.product_id as product_id_bought_with
from mytable a
join mytable b
ON a.ID_TICKET   = b.ID_TICKET       
AND a.DAT_VTE = b.DAT_VTE
and a.rownum <b.rownum
)
GROUP BY DAT_VTE, product_id, product_id_bought_with

根据您提供的错误，资源超出错误通常是在操作需要在单个计算单元上收集所有数据时触发的，如果不适合，则作业将失败。对大量数据进行排序涉及大量计算资源，如果使用分区，这些资源可以得到更好的利用。

以下是解决问题的方法：

1通常分区有助于解决文档和本链接中给出的资源问题。

2您也可以尝试拆分查询，将每个单独的子/内部查询的结果写入另一个表，作为进一步处理的临时存储空间。