Oracle根据条件过滤掉重复的条目

Question

我有以下查询来生成按日期计算的sale_price:

SELECT 
product_name, 
SUM(sale_price) top_sale_price, 
sale_date,
COUNT(*) count
FROM sales
WHERE sale_date IN
    (TO_DATE ('14-JUN-14', 'DD-MON-YY'),
    TO_DATE ('14-JUN-14', 'DD-MON-YY') - 1,
    TO_DATE ('14-JUN-14', 'DD-MON-YY') - 7,
    TO_DATE ('14-JUN-14', 'DD-MON-YY') - 2 * 7,
    TO_DATE ('14-JUN-14', 'DD-MON-YY') - 3 * 7,
    TO_DATE ('14-JUN-14', 'DD-MON-YY') - 4 * 7,
    TO_DATE ('14-JUN-14', 'DD-MON-YY') - 5 * 7,
    TO_DATE ('14-JUN-14', 'DD-MON-YY') - 6 * 7,
    TO_DATE ('14-JUN-14', 'DD-MON-YY') - 7 * 7,
    TO_DATE ('14-JUN-14', 'DD-MON-YY') - 8 * 7,
    TO_DATE ('14-JUN-14', 'DD-MON-YY') - 9 * 7,
    TO_DATE ('14-JUN-14', 'DD-MON-YY') - 10 * 7)
GROUP BY 
product_name, 
sale_date

我的结果看起来像这样:

product_name     top_sale_price     sale_date     count
shoes            10.00              01-JUL-14     2
hat              11.00              30-JUN-14     1
shirt            12.00              24-JUN-14     3
...

我想根据某种逻辑从每个分组中只选择一件衬衫或帽子。例如，假设有一个"is_valid_purchase"行。如果分组中有一个或多个项目，比如'shoes'和'shirt'，我想选择将'is_valid_purchase'设置为true的项目(注意，在本例中，group by中只有一个项目将'is_valid_purchase'设置为true)。我如何修改我的sql来做我所描述的?

Answer 1

这是你想要的吗?

with t as (
      SELECT product_name, SUM(sale_price) top_sale_price, sale_date, COUNT(*) count
      FROM sales
      WHERE sale_date IN
          (TO_DATE ('14-JUN-14', 'DD-MON-YY'),
          TO_DATE ('14-JUN-14', 'DD-MON-YY') - 1,
          TO_DATE ('14-JUN-14', 'DD-MON-YY') - 7,
          TO_DATE ('14-JUN-14', 'DD-MON-YY') - 2 * 7,
          TO_DATE ('14-JUN-14', 'DD-MON-YY') - 3 * 7,
          TO_DATE ('14-JUN-14', 'DD-MON-YY') - 4 * 7,
          TO_DATE ('14-JUN-14', 'DD-MON-YY') - 5 * 7,
          TO_DATE ('14-JUN-14', 'DD-MON-YY') - 6 * 7,
          TO_DATE ('14-JUN-14', 'DD-MON-YY') - 7 * 7,
          TO_DATE ('14-JUN-14', 'DD-MON-YY') - 8 * 7,
          TO_DATE ('14-JUN-14', 'DD-MON-YY') - 9 * 7,
          TO_DATE ('14-JUN-14', 'DD-MON-YY') - 10 * 7) and
         is_valid_purchase = 1
      GROUP BY product_name, sale_date
     )
select *
from t;