SQL Server 如何选择在每次连续订单中增加订单数量的买家

pexxcrt2 于 2022-12-10 发布在其他

关注(0)|答案(2)|浏览(161)

I have table Buyer
| BuyId | QuantityOrdered | dateordered |
| ------------ | ------------ | ------------ |
| 1 | 10 | 2021-11-04 |
| 1 | 20 | 2022-01-22 |
| 2 | 50 | 2022-02-20 |
| 2 | 60 | 2022-05-02 |
| 3 | 10 | 2022-05-02 |
| 4 | 10 | 2022-05-02 |
I need to select all BuyId's who consecutively had higher QuantityOrder in each new record

buyid=1 had first order with quantity=10, second order with quantity=20
buyid=2 had first order with quantity=50, second order with quantity=60

So BuyId=1 and 2 would enter the results, while 3 and 4 would be filtered out because they had only one order or they did not have orders with consecutively higher quantities ordered
I tried with this, and I'm aware that this query gives me only buyid's who have more than one order, but I am missing the rule where I have to filter results out by quantity increased with each new order

select buyid, count(*) as ordered
from buyer
group by buyid
having count(*) >1

How would I write out that rule in a query, to select only BuyId's who had multiple orders, and in each new order they ordered higher quantities than in previous orders?

sql-server

来源：https://stackoverflow.com/questions/74669688/how-to-select-buyers-who-increased-their-order-quantity-with-each-consecutive-or

2条答案

按热度按时间

zfciruhq1#

Taking advantage of the fact that count() doesn't takes into account NULL values:

select buyid from (
select buyid, count(QuantityOrdered) as norders, count(mod) as nqttyincreasing
from (
    select d.*, 
        case when nvl(lag(QuantityOrdered) over(partition by buyid order by dateordered),0) < QuantityOrdered then 1 end as mod
    from data d
)
group by buyid
) where norders = nqttyincreasing ;

赞(0）回复(0）举报 2022-12-10

carvr3hs2#

With this query, it will check if all purchases as bigger in quantity as the last and will not show buyers id. that have only one purchase or sold equal or less than the last time4

CREATE TABLE buyers
    ([BuyId] int, [QuantityOrdered] int, [dateordered] date)
;
    
INSERT INTO buyers
    ([BuyId], [QuantityOrdered], [dateordered])
VALUES
    (1, 10, '2021-11-04'),
    (1, 20, '2022-01-22'),
  (1, 30, '2022-02-22'),
    (2, 50, '2022-02-20'),
    (2, 60, '2022-05-02'),
    (3, 60, '2022-05-02'),
    (4, 10, '2022-05-02'),
    (4, 6, '2022-06-02')
;

8 rows affected

WITH CTE as (SELECT
[BuyId], [QuantityOrdered], [dateordered],
  (SELECT COUNT(*) FROM buyers b1 WHERE b1.[BuyId] = buyers.[BuyId]) _count_
,CASE WHEn [QuantityOrdered] > 
   LAG([QuantityOrdered]) OVER(PARTITION BY [BuyId] ORDER BY [dateordered])
THEn 1 
   ELSe 
      CASE WHEN LAG([QuantityOrdered]) OVER(PARTITION BY [BuyId] ORDER BY [dateordered]) IS NULL 
           THEN 1 ELSE 0 END
  END _bigger_
FROM buyers)
SELECT DISTINCT [BuyId] FROM CTE 
  WHERE _count_ > 1 AND NOT EXISTS ( SELECT 1 FROM CTE c1 
                     WHERE c1.[BuyId] = CTE.[BuyId] AND c1._bigger_ = 0)

BuyId
1
2

fiddle

赞(0）回复(0）举报 2022-12-10

我来回答

SQL Server 如何选择在每次连续订单中增加订单数量的买家

2条答案

相关问题

热门标签

最新问答