識別具有下降趨勢的用戶SQL

試圖確定數量比上次購買減少的客戶串列。

在這個例子中，我們看到每次新購買瑪麗的數量都會隨著時間的推移而減少。然而，雖然 Bob 表現出下降，但他不會在結果中屈服，因為他在 9/19 購買了 8 件，這比他之前購買的 5 件多。

我正在努力為我的生活找出一個問題，我似乎無法把它放在一起

Customer    PurchaseDate    Quantity
Bob         9/1/2021        10
Bob         9/10/2021       6
Bob         9/18/2021       5
Bob         9/19/2021       8
Mary        9/1/2021        10
Mary        9/10/2021       6
Mary        9/18/2021       5
Mary        9/19/2021       3
Frank       9/1/2021        5
Lucus       9/1/2021        5
Lucus       9/10/2021       6
Lucus       9/18/2021       10

最終結果應該是

Customer
Mary

uj5u.com熱心網友回復：

這有點棘手，要找到穩定增加或減少的結果，您可能需要使用MATCH_RECOGNIZEMySQL（尚）不支持的子句。通過這種方式，您可以定義一個模式，其中每個數量都小于前一個值。此外，您可能可以使用遞回 cte 來做到這一點，但這超出了我的能力范圍。

這是我想出的，但需要注意的是它只比較第一個和最后一個值：

WITH
    tbl (customer, purchasedate, quantity) AS (
SELECT * FROM VALUES 
    ('Bob',         '9/1/2021',        10),
    ('Bob',         '9/10/2021',       6),
    ('Bob',         '9/18/2021',       5),
    ('Bob',         '9/19/2021',       8),
    ('Mary',        '9/1/2021',        10),
    ('Mary',        '9/10/2021',       6),
    ('Mary',        '9/18/2021',       5),
    ('Mary',        '9/19/2021',       3),
    ('Frank',       '9/1/2021',        5),
    ('Lucus',       '9/1/2021',        5),
    ('Lucus',       '9/10/2021',       6),
    ('Lucus',       '9/18/2021',       10)
)

SELECT
    DISTINCT customer
FROM
    tbl
QUALIFY
      FIRST_VALUE(quantity) OVER (partition BY customer ORDER BY purchasedate)
    > LAST_VALUE(quantity)  OVER (PARTITION BY customer ORDER BY purchasedate)

這使：

CUSTOMER
Bob
Mary

或者，要以已知的最大值嚴格減少，您可以將它們全部鏈接在一起，這會變得非常丑陋：

WITH
    tbl (customer, purchasedate, quantity) AS (
SELECT * FROM VALUES 
    ('Bob',         '9/1/2021',        10),
    ('Bob',         '9/10/2021',       6),
    ('Bob',         '9/18/2021',       5),
    ('Bob',         '9/19/2021',       8),
    ('Mary',        '9/1/2021',        10),
    ('Mary',        '9/10/2021',       6),
    ('Mary',        '9/18/2021',       5),
    ('Mary',        '9/19/2021',       3),
    ('Frank',       '9/1/2021',        5),
    ('Lucus',       '9/1/2021',        5),
    ('Lucus',       '9/10/2021',       6),
    ('Lucus',       '9/18/2021',       10)
)

SELECT
    DISTINCT customer
FROM
    tbl
    qualify 
        (NTH_VALUE(quantity, 1) OVER (partition BY customer ORDER BY purchasedate) >= NTH_VALUE(quantity, 2) OVER (partition BY customer ORDER BY purchasedate))
        and ((NTH_VALUE(quantity, 2) OVER (partition BY customer ORDER BY purchasedate) >= NTH_VALUE(quantity, 3) OVER (partition BY customer ORDER BY purchasedate)) or (NTH_VALUE(quantity, 3) OVER (partition BY customer ORDER BY purchasedate) is null))
        and ((NTH_VALUE(quantity,3) OVER (partition BY customer ORDER BY purchasedate) >= NTH_VALUE(quantity, 4) OVER (partition BY customer ORDER BY purchasedate)) or (NTH_VALUE(quantity, 4) OVER (partition BY customer ORDER BY purchasedate) is null))

這使：

CUSTOMER
Mary

盡管對于未知數量，我認為match_recognize這將是最好的解決方案（或者您可以添加一些遞回或自定義函式）。

uj5u.com熱心網友回復：

SELECT Customer
FROM ( SELECT CASE WHEN Customer = @customer AND Quantity > @quantity
                   THEN 1
                   ELSE 0 
                   END AS increase_detected,
              @customer := Customer Customer,
              PurchaseDate,
              @quantity := Quantity Quantity
       FROM test
       CROSS JOIN ( SELECT @customer := NULL, @quantity := NULL ) init_variables
       ORDER BY Customer, PurchaseDate
     ) subquery
GROUP BY Customer
HAVING NOT SUM(increase_detected);

https://dbfiddle.uk/?rdbms=mysql_5.6&fiddle=68b75b0df7fe4b383896e78db0caa569

標籤：mysql sql

上一篇：使用重復值重新編號SQL查詢中的行

下一篇：在pl/pgsql-while回圈中更新單行表