sql - 如何使用分析获取唯一 ID 的汇总总数？

Question

我的重复表有重复的 id，但我想要关于唯一 id 的汇总统计信息。

 Detail_id   code   book   tree
----------- ------ ------ ------
  1          BR54   COOK   PINE
  1          BR55   COOK   PINE
  1          BR51   COOK   PINE
  2          BR55   COOK   MAPL
  2          BR60   COOK   MAPL
  3          BR61   FORD   PINE
  3          BR54   FORD   PINE
  3          BR55   FORD   PINE

这是我在 SQLFiddle 上的查询

select count(case detail_book when 'COOK' THEN 1 else 0 end) as cook_total,
       count(case detail_book when 'FORD' THEN 1 else 0 end) as ford_total,
       count(case detail_tree when 'PINE' THEN 1 else 0 end) as pine_total,
       count(case detail_book when 'MAPL' THEN 1 else 0 end) as mapl_total
  from detail_records;

想要的结果:

COOK_TOTAL FORD_TOTAL PINE_TOTAL MAPL_TOTL
---------- ---------- ---------- ----------
  2          1          2         1

Answer 1

您可以使用分析函数和内联 View 来避免重复计数问题:

select sum(case when detail_book = 'COOK' and book_cntr = 1 then 1 else 0 end) as cook_total,
       sum(case when detail_book = 'FORD' and book_cntr = 1 then 1 else 0 end) as ford_total,
       sum(case when detail_tree = 'PINE' and tree_cntr = 1 then 1 else 0 end) as pine_total,
       sum(case when detail_tree = 'MAPL' and tree_cntr = 1 then 1 else 0 end) as mapl_total
  from (select d.*,
               row_number() over(partition by detail_book, detail_id order by detail_book, detail_id) as book_cntr,
               row_number() over(partition by detail_tree, detail_id order by detail_tree, detail_id) as tree_cntr
          from detail_records d) v

fiddle : http://sqlfiddle.com/#!4/889a8/31/0