雪花云数据平台我需要帮助在查询中使用Analytical或Aggregate函数

Question

SELECT pid,oid,
item_to_buy AS item_purchase_count
FROM
(SELECT distinct A.pid,A.oid,
B.item_to_buy
//row_number()over(partition by A.oid order by item_to_buy) as RNK
FROM table A
JOIN table B
ON A.product_id=B.product_id
WHERE date='2022-03-03'
AND A.oid IS NOT NULL
AND A.oid=34721248
)

当我运行上面的查询时，我得到了下面提到的值：

PID          OID      ITEM_PURCHASE_COUNT
93012594908 10021248    NULL
93012594908 10021248    0
93012594908 10021248    1

我只想显示结果中的第三行。即，结果应如下所示：

PID          OID      ITEM_PURCHASE_COUNT
93012594908 10021248    1

我尝试使用max、row_number、rank、dense_rank，但没有得到正确的结果。

需要这方面的指导。

Answer 1

你试过吗

WHERE item_purchase_count = 1

或者你想说你想要最大的。因此，合格的

QUALIFY row_number() over (partition by pid, oid order by item_purchase_count desc ) = 1

因此：

SELECT 
pid,
oid,
item_to_buy AS item_purchase_count
FROM (
SELECT distinct 
A.pid,
A.oid,
B.item_to_buy
FROM table A
JOIN table B
ON A.product_id = B.product_id
WHERE date='2022-03-03'
AND A.oid IS NOT NULL
AND A.oid = 34721248
)
QUALIFY row_number() over (partition by pid, oid order by item_purchase_count desc ) = 1

或者实际上：

SELECT distinct 
A.pid,
A.oid,
B.item_to_buy AS item_purchase_count
FROM table A
JOIN table B
ON A.product_id = B.product_id
WHERE date='2022-03-03'
AND A.oid IS NOT NULL
AND A.oid = 34721248
QUALIFY row_number() over (partition by A.pid, A.oid order by item_purchase_count desc ) = 1

或者，如果你想用长格式写：

SELECT 
pid,
oid,
item_to_buy AS item_purchase_count
FROM (
SELECT distinct 
A.pid,
A.oid,
B.item_to_buy
row_number() over (partition by A.pid, A.oid order by B.item_to_buy desc ) as rnk
FROM table A
JOIN table B
ON A.product_id = B.product_id
WHERE date='2022-03-03'
AND A.oid IS NOT NULL
AND A.oid=34721248
)
WHERE rnk = 1;

有助于如果你对数据进行分区，手机不是写答案的最佳方式。叹息