计算案件中的案件数目

Question

我想数数在某一特定年份里在某一家商店消费一定数额的人的数量。

我尝试创建一个嵌套的案例表达式，第一个用于查看特定年份的事务，第二个查看一个人到某个商店的事务的总和，但是Oracle不喜欢嵌套的案例表达式(除非我做错了)。

来自此SQL示例

CREATE TABLE table_name ( PersonId, StoreId, AmountSpent, Year) as
  select 1, 1, 60, 2017 from dual union
  select 1, 1, 50, 2017  from dual union
  select 1, 2, 70, 2018 from dual union
  select 2, 1, 10, 2017 from dual union
  select 2, 1, 10, 2017  from dual union
  select 2, 1, 200, 2018  from dual union
  select 2, 2, 60, 2018 from dual union
  select 2, 2, 60, 2018  from dual union
  select 3, 1, 25, 2017 from dual union
  select 3, 2, 200, 2017 from dual union
  select 3, 2, 200, 2018  from dual;

Select
  StoreId,
  SUM(CASE WHEN Year = '2017'
        THEN AmountSpent
        ELSE 0
      End) Year17,
   SUM(CASE WHEN Year = '2018'
        THEN AmountSpent
        ELSE 0
      End) Year18
FROM table_name
GROUP BY StoreId;

   STOREID     YEAR17     YEAR18
---------- ---------- ----------
         1        145        200
         2        200        330

有人能做出这样的输出吗？我认为有些数字可能是错误的，但似乎大多数人都明白我要去哪里的要旨。

+----+---------+------------+---------------------+-------------------+---------------------+---------------------+------------+-------------------+---------------------+
|    | STOREID | Y17_INCOME | Y17_SPENT_BELOW_100 | Y17_SPENT_100_150 | Y17_SPENT_ABOVE_150 | Y18_INCOME | Y18_SPENT_BELOW_100 | Y18_SPENT_100_150 | Y18_SPENT_ABOVE_150 |
+----+---------+------------+---------------------+-------------------+---------------------+------------+---------------------+-------------------+---------------------+
| 1  | 1       | 145        | 2                   | 1                 | 0                   | 200        | 2                   | 0                 | 1                   |
+----+---------+------------+---------------------+-------------------+---------------------+---------------------+------------+-------------------+---------------------+
| 2  | 2       | 200        | 0                   | 0                 | 1                   | 330        | 0                   | 0                 | 1                   |
+----+---------+------------+---------------------+-------------------+---------------------+------------+---------------------+-------------------+---------------------+

不确定这是否有可能，但如果可能的话，那就太好了！

Answer 1

我不认为你的数据符合你的预期输出。

您需要一个子查询，在该查询中，您每年对每个商店的每个人的支出进行汇总(按商店和个人分组)。然后，您只需要按商店分组，并使用sum检索给定年份的收入。count + case + distinct是计算花费了钱的唯一个人is并将其分配给and组(每年三列之一)的关键。

请注意，yearXX_spending列已经保存了一个人用于给定存储和年份的金额之和(这是来自子查询的)：

select
    storeid
  , sum(year17_spending) as y17_income
  , count(distinct case when year17_spending < 100 then personid end) as y17_spent_below_100
  , count(distinct case when year17_spending between 100 and 150 then personid end) as y17_spent_100_150
  , count(distinct case when year17_spending > 150 then personid end) as y17_spent_above_150
  , sum(year18_spending) as y18_income
  , count(distinct case when year18_spending < 100 then personid end) as y18_spent_below_100
  , count(distinct case when year18_spending between 100 and 150 then personid end) as y18_spent_100_150
  , count(distinct case when year18_spending > 150 then personid end) as y18_spent_above_150
from (
  select 
      storeid
    , personid
    , sum(case when year = 2017 then amountspent end) as year17_spending
    , sum(case when year = 2018 then amountspent end) as year18_spending
  from table_name
  group by storeid, personid
  ) t
group by storeid

样本数据的输出：

+----+---------+------------+---------------------+-------------------+---------------------+---------------------+------------+-------------------+---------------------+
|    | STOREID | Y17_INCOME | Y17_SPENT_BELOW_100 | Y17_SPENT_100_150 | Y17_SPENT_ABOVE_150 | Y18_INCOME | Y18_SPENT_BELOW_100 | Y18_SPENT_100_150 | Y18_SPENT_ABOVE_150 |
+----+---------+------------+---------------------+-------------------+---------------------+------------+---------------------+-------------------+---------------------+
| 1  | 1       | 145        | 2                   | 1                 | 0                   | 200        | 2                   | 0                 | 1                   |
+----+---------+------------+---------------------+-------------------+---------------------+---------------------+------------+-------------------+---------------------+
| 2  | 2       | 200        | 0                   | 0                 | 1                   | 330        | 0                   | 0                 | 1                   |
+----+---------+------------+---------------------+-------------------+---------------------+------------+---------------------+-------------------+---------------------+

还请注意，通过在构建示例数据时执行UNION，可以消除重复数据，从而使行：

select 2, 1, 10, 2017

只有一次(而不是两次)。如果您打算在不删除重复项的情况下包含所有内容，请使用UNION ALL。

Answer 2

您可以在内联视图中进行存储/人员汇总，该视图本身会生成：

SELECT
  StoreId,
  PersonId,
  Year,
  SUM(AmountSpent) AS TotalSpent
FROM table_name
GROUP BY
  StoreId,
  PersonId,
  Year
ORDER BY
  StoreId,
  Year,
  PersonId;

   STOREID   PERSONID       YEAR TOTALSPENT
---------- ---------- ---------- ----------
         1          1       2017        110
         1          2       2017         10
         1          3       2017         25
         1          2       2018        200
         2          3       2017        200
         2          1       2018         70
         2          2       2018         60
         2          3       2018        200

然后在外部查询中使用多个单独的案例表达式和聚合：

SELECT
  StoreId,
  SUM(CASE WHEN Year = '2017'
        THEN TotalSpent
        ELSE 0
      END) Year17,
  COUNT(CASE WHEN Year = '2017'
        AND TotalSpent < 100
        THEN TotalSpent
      END) AS Year17lt100,
  COUNT(CASE WHEN Year = '2017'
        AND TotalSpent >= 100
        AND TotalSpent < 150
        THEN TotalSpent
      END) AS Year17gte100lt150,
  COUNT(CASE WHEN Year = '2017'
        AND TotalSpent >= 150
        THEN TotalSpent
      END) AS Year17gte150,
   SUM(CASE WHEN Year = '2018'
        THEN TotalSpent
        ELSE 0
      END) Year18,
  COUNT(CASE WHEN Year = '2018'
        AND TotalSpent < 100
        THEN TotalSpent
      END) AS Year18lt100,
  COUNT(CASE WHEN Year = '2018'
        AND TotalSpent >= 100
        AND TotalSpent < 150
        THEN TotalSpent
      END) AS Year18gte100lt150,
  COUNT(CASE WHEN Year = '2017'
        AND TotalSpent >= 150
        THEN TotalSpent
      END) AS Year18gte150
FROM (
  SELECT
    StoreId,
    PersonId,
    Year,
    SUM(AmountSpent) AS TotalSpent
  FROM table_name
  GROUP BY
    StoreId,
    PersonId,
    Year
)
GROUP BY StoreId
ORDER BY StoreId;

这会得到：

   STOREID     YEAR17 YEAR17LT100 YEAR17GTE100LT150 YEAR17GTE150     YEAR18 YEAR18LT100 YEAR18GTE100LT150 YEAR18GTE150
---------- ---------- ----------- ----------------- ------------ ---------- ----------- ----------------- ------------
         1        145           2                 1            0        200           0                 0            0
         2        200           0                 0            1        330           2                 0            1

但我觉得这是不对的.

我也从你的列标题中略作改动，但是如果你真的想要你展示的东西，你可以调整那些。

如果您使用的是支持pivot()的版本，您还可以使用一个子句来减少代码和重复次数；该版本可以提供多个输出并允许条件聚合：

SELECT *
FROM (
  SELECT
    StoreId,
    Year,
    SUM(AmountSpent) AS TotalSpent
  FROM table_name
  GROUP BY
    StoreId,
    PersonId,
    Year
)
PIVOT (
  SUM(TotalSpent) as total,
  COUNT(CASE WHEN TotalSpent < 100 THEN TotalSpent END) as lt100,
  COUNT(CASE WHEN TotalSpent >= 100 AND TotalSpent < 150 THEN TotalSpent END) as gte100lt150,
  COUNT(CASE WHEN TotalSpent >= 150 THEN TotalSpent END) as gte150
  FOR Year IN (2017 as year17, 2018 as year18)
)
ORDER BY StoreId;

   STOREID YEAR17_TOTAL YEAR17_LT100 YEAR17_GTE100LT150 YEAR17_GTE150 YEAR18_TOTAL YEAR18_LT100 YEAR18_GTE100LT150 YEAR18_GTE150
---------- ------------ ------------ ------------------ ------------- ------------ ------------ ------------------ -------------
         1          145            2                  1             0          200            0                  0             1
         2          200            0                  0             1          330            2                  0             1

Oracle将对其进行扩展，使其看起来像上面最初的更长的查询，但维护起来更容易。

更新的SQL Fiddle。