如何选择每次连续订单都增加订单数量的买家

我有一个表Buyer

tbody> <<tr>1224

BuyId	定量订购	日期订购
1	10	2021-11-04
	20	2022-01-22
	50	2022-02-20
	60	2022-05-02
3	10	2022-05-02
	10	2022-05-02

利用count()不考虑NULL值的事实:

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 ;

使用此查询，它将检查所有购买的数量是否与上次购买的数量相同，并且不会显示买家id。只有一次购买或销售等于或少于最后一次的

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

相关内容

最新更新

热门标签：