基于指定条件排除SQL记录：需求说明与数据示例

需求说明 #

需要排除满足以下条件的记录：

• 同一客户在同一天购买了Shoe和Hat，排除Hat对应的记录
• 同一客户在同一天购买了Shoe和Cloth，排除Cloth对应的记录

示例数据 #

create table tbl
(
  Customer  varchar(5),
  Day date,
  Product  varchar(5),
  Cost  decimal(10,2)
);

insert into tbl values 
('A',       '1/1/2019',      'Shoe',        10.00),
('B',       '2/4/2021',      'Hat',          10.00),
('B',       '5/8/2018',      'Shoe',         10.00),
('C',       '6/6/2020',      'Hat',         10.00),
('C',       '11/9/2021',      'Cloth',       10.00),
('E',       '7/2/2020',      'Hat',        10.00),
('A',       '7/5/2022',      'Hat',       10.00),
('C',       '6/6/2020',      'Shoe',        10.00),
('A',       '1/1/2019',      'Cloth',        10.00),
('E',       '7/2/2020',      'Shoe',       10.00);

解决方案SQL #

SELECT Customer, Day, Product, Cost
FROM (
    SELECT 
        *,
        COUNT(CASE WHEN Product = 'Shoe' THEN 1 END) OVER (PARTITION BY Customer, Day) AS has_shoe
    FROM tbl
) filtered_data
WHERE NOT (
    (has_shoe > 0 AND Product = 'Hat') 
    OR (has_shoe > 0 AND Product = 'Cloth')
)
ORDER BY Customer, Day;

思路解释 #

1. 内层查询用窗口函数COUNT() OVER (PARTITION BY Customer, Day)，按客户和日期分组，统计每组内购买Shoe的次数，得到has_shoe字段——值大于0表示该客户当天买过Shoe。
2. 外层查询过滤掉两种需要排除的记录：
   • 当天买过Shoe且当前产品是Hat
   • 当天买过Shoe且当前产品是Cloth

预期结果 #

结果说明 #

• 客户A在2019-01-01同时购买了Shoe和Cloth，Cloth记录被排除
• 客户C在2020-06-06同时购买了Shoe和Hat，Hat记录被排除
• 客户E在2020-07-02同时购买了Shoe和Hat，Hat记录被排除
• 其余未触发排除条件的记录均保留

内容的提问来源于stack exchange，提问作者Ben