這兩個查詢會產生相同的結果嗎（使用視窗函式與分組依據）

我在“Ace the Data Science Interview”中遇到了這個問題：

“假設給您下表，其中包含有關用戶購買的資訊。撰寫查詢以獲取在多天內至少購買了一種或多種相同產品的人數。”

購買

我想出了以下查詢：

SELECT 
    count(DISTINCT user_id) as num_ppl_w_multiple_purchases
FROM (
    SELECT
        user_id,
        count(*) as num_product_purchases /* only increments for purchases made on different days */
    FROM
        purchases
    GROUP BY
        user_id,
        product_id,
        DATE(purchase_time)
    HAVING
        num_product_purchases > 1
) t

這本書提供了這個解決方案：

SELECT
    COUNT(DISTINCT user_id)
FROM
    (
        SELECT
            user_id,
            RANK() OVER (
                PARTITION BY user_id,
                product_id
                ORDER BY
                    CAST(purchase_time as DATE)
            ) AS purchase_no
        FROM
            purchases
    ) t
WHERE
    purchase_no = 2

我無法驗證這兩個查詢是否實作相同的目標。有人可以肯定我的查詢是正確的，如果不是，為什么不呢？

編輯回應比爾卡爾文的回應修改查詢：

SELECT 
    count(DISTINCT user_id) as num_ppl_w_multiple_purchases
FROM (
    SELECT
        user_id,
        count(*) as num_product_purchases
    FROM (
        SELECT
            DISTINCT user_id, product_id, DATE(purchase_time)
        FROM
            purchases
    ) t1
    GROUP BY
        user_id,
        product_id
    HAVING
        num_product_purchases > 1
) t2

uj5u.com熱心網友回復：

您的查詢看起來會匹配在同一天多次購買相同產品的用戶，但它不會找到在多天購買相同產品的用戶。您的 GROUP BY 條件包括日期，因此計數將僅包括每個日期的那些條目。

 ------------- --------- ------------ ---------- ------- --------------------- 
| purchase_id | user_id | product_id | quantity | price | purchase_time       |
 ------------- --------- ------------ ---------- ------- --------------------- 
|           1 |       2 |         10 |        1 |    10 | 2022-04-20 00:00:00 |
|           2 |       2 |         10 |        1 |    10 | 2022-04-21 00:00:00 |
|           3 |       8 |         10 |        1 |    10 | 2022-04-19 00:00:00 |
|           4 |       8 |         10 |        1 |    10 | 2022-04-19 00:00:00 |
 ------------- --------- ------------ ---------- ------- --------------------- 

我將通過使用您的部分子查詢來演示：

SELECT user_id, date(purchase_time), count(*) as num_product_purchases 
FROM purchases GROUP BY user_id, product_id, DATE(purchase_time);

 --------- --------------------- ----------------------- 
| user_id | date(purchase_time) | num_product_purchases |
 --------- --------------------- ----------------------- 
|       2 | 2022-04-20          |                     1 |
|       2 | 2022-04-21          |                     1 |
|       8 | 2022-04-19          |                     2 |
 --------- --------------------- ----------------------- 

用戶 2 在兩個不同的日子里進行了購買，但由于每一天都在自己的組中，因此它們都被計為 1。因此它們將被 HAVING 條件排除在外。只有用戶 8 計數為 2，因為他們在同一天購買了他們的產品。

但是用戶 8 不應該被算作多天購買了該產品，因為他們只購買了一天。所以結果對兩個用戶都是錯誤的。

測驗更新查詢的結果（只是子查詢的一部分，因此我們可以看到中間結果）：

SELECT user_id, count(*) as num_product_purchases FROM (
  SELECT DISTINCT user_id, product_id, DATE(purchase_time) FROM purchases) t1 
GROUP BY user_id, product_id;

 --------- ----------------------- 
| user_id | num_product_purchases |
 --------- ----------------------- 
|       2 |                     2 |
|       8 |                     1 |
 --------- ----------------------- 

這樣看起來更好。我想不出另一個會出錯的資料樣本。

標籤：mysql sql

上一篇：SQL查詢未產生預期結果

下一篇：連接到使用Docker安裝的Laravel9中的mySQL