Oracle中的代表性月销售额汇总

3z6pesqy 于 2023-10-16 发布在 Oracle

关注(0)|答案(2)|浏览(117)

该请求的目标是获得每个代表在过去3年的月销售额表。它应该看起来像这样：
| 卖家ID|卖方|月| 2023 | 2022 | 2021 |
| --|--|--|--|--|--|
| 0001 |JohnSmith|一月| 0 | 2400 |1987.34|
| 0001 |JohnSmith|二月|二二一九点一七|2000.01| 2211.12|
| 0001 |JohnSmith|三月|1983.62| 2173.02| 2002.27|
| 0001 |JohnSmith|四月|一七四八点二一|小行星1943.21| 2203.82|
| 0001 |JohnSmith|可以|2083.62| 2073.77|二一零一点三九|
| 0002 |简·史密斯|一月|1684.02| 2400 |1987.34|
| 0002 |简·史密斯|二月|二四一九点一七|1900.01| 2221.12|
| 0002 |简·史密斯|三月|一八八三点六二|2273.02| 2012.27|
首先，基本的SQL请求是这样的：

select c.SELLER_ID, r.NAME as SELLER,
       extract(year from c.DATE) as YEAR,
       to_char(c.DATE, 'YYYY-MM') as MONTH,
       to_char(c.DATE, 'Month') as MONTH_NAME,
       sum(c.S_TOTAL) as MONTHLY_TOTAL
  from SALE_HEADER c,
       SALER r
 where c.SELLER_ID=r.ID
   and extract(year from c.DATE) between extract(year from sysdate) -2 and extract(year from sysdate)
 group by SELLER_ID, r.NAME, extract(year from c.DATE), to_char(c.DATE, 'YYYY-MM'), to_char(c.DATE, 'Month'))
 order by SELLER_ID, to_char(c.DATE, 'YYYY-MM')

它返回如下内容：
| 销售员ID| Saler|年|月|月份名称|每月总计|
| --|--|--|--|--|--|
| 0001 |JohnSmith| 2022 |2022-02 -02|二月|2000.01|
| 0001 |JohnSmith| 2023 |2023-02年|二月|二二一九点一七|
然后将其用作此透视查询中的子查询：

select SELLECR_ID, SELLER, substr(MONTH, 6,2) as MONTH, MONTH_NAME, "2021", "2022", "2023" from (
  -- preceding query as a subquery
)
pivot (
  sum(MONTHLY_TOTAL)
  for YEAR in (
    2021,
    2022,
    2023
  )
)
group by SELLER_ID, SELLER, substr(MONTH, 6,2), MONTH_NAME, "2021", "2022", "2023"
order by SELLER_ID, substr(MONTH, 6,2)

它在单独的行上返回数据，如：
| 销售员ID| Saler|月|月份名称| 2023 | 2022 | 2021 |
| --|--|--|--|--|--|--|
| 0001 |JohnSmith| 02 |二月||2000.01||
| 0001 |JohnSmith| 02 |二月|二二一九点一七|||
为什么这两条线不在一条线上？

oracle

来源：https://stackoverflow.com/questions/77129013/summary-of-representative-monthly-sales-in-oracle

2条答案

按热度按时间

yc0p9oo01#

为什么这两条线不在一条线上？
因为你是按月和年计算的。
您可以按月份进行聚合，然后使用条件聚合来获取月份的年份数据：

SELECT c.seller_id,
       MAX(s.name) as seller,
       TO_CHAR(c."DATE", 'MM') AS month,
       TO_CHAR(c."DATE", 'Month') AS month_name,
       SUM(
         CASE
         WHEN EXTRACT(YEAR FROM c."DATE") = 2023
         THEN c.s_total
         END
       ) AS "2023",
       SUM(
         CASE
         WHEN EXTRACT(YEAR FROM c."DATE") = 2022
         THEN c.s_total
         END
       ) AS "2022",
       SUM(
         CASE
         WHEN EXTRACT(YEAR FROM c."DATE") = 2021
         THEN c.s_total
         END
       ) AS "2021"
FROM   sale_header c
       INNER JOIN saler s
       ON c.SELLER_ID=r.ID
WHERE  c."DATE" >= DATE '2021-01-01'
AND    c."DATE" <  DATE '2024-01-01'
GROUP BY
       c.seller_id,
       TO_CHAR(c."DATE", 'MM'),
       TO_CHAR(c."DATE", 'Month')

或：

SELECT *
FROM   (
  SELECT c.seller_id,
         s.name as seller,
         TO_CHAR(c."DATE", 'MM') AS month,
         TO_CHAR(c."DATE", 'Month') AS month_name,
         EXTRACT(YEAR FROM c."DATE") AS year,
         c.s_total
  FROM   sale_header c
         INNER JOIN saler s
         ON c.SELLER_ID=s.ID
  WHERE  c."DATE" >= DATE '2021-01-01'
  AND    c."DATE" <  DATE '2024-01-01'
)
PIVOT (
  SUM(s_total)
  FOR year IN (
    2023 AS "2023",
    2022 AS "2022",
    2021 AS "2021"
  )
)

其中，对于样本数据：

CREATE TABLE sale_header (seller_id, "DATE", s_total) AS
SELECT '0001', DATE '2023-01-01',  900 FROM DUAL UNION ALL
SELECT '0001', DATE '2023-02-01',  800 FROM DUAL UNION ALL
SELECT '0001', DATE '2023-03-01',  700 FROM DUAL UNION ALL
SELECT '0001', DATE '2022-01-01',  600 FROM DUAL UNION ALL
SELECT '0001', DATE '2022-02-01',  500 FROM DUAL UNION ALL
SELECT '0001', DATE '2022-01-01',  400 FROM DUAL UNION ALL
SELECT '0001', DATE '2022-03-01',  300 FROM DUAL UNION ALL
SELECT '0001', DATE '2021-01-01',  200 FROM DUAL UNION ALL
SELECT '0001', DATE '2021-02-01',  100 FROM DUAL UNION ALL
SELECT '0001', DATE '2021-03-01',    0 FROM DUAL UNION ALL
SELECT '0002', DATE '2023-01-01', 9000 FROM DUAL UNION ALL
SELECT '0002', DATE '2023-02-01', 8000 FROM DUAL UNION ALL
SELECT '0002', DATE '2023-03-01', 7000 FROM DUAL UNION ALL
SELECT '0002', DATE '2022-01-01', 6000 FROM DUAL UNION ALL
SELECT '0002', DATE '2022-02-01', 5000 FROM DUAL UNION ALL
SELECT '0002', DATE '2022-01-01', 4000 FROM DUAL UNION ALL
SELECT '0002', DATE '2022-03-01', 3000 FROM DUAL UNION ALL
SELECT '0002', DATE '2021-01-01', 2000 FROM DUAL UNION ALL
SELECT '0002', DATE '2021-02-01', 1000 FROM DUAL UNION ALL
SELECT '0002', DATE '2021-03-01',  999 FROM DUAL;

CREATE TABLE saler (id, name) AS
  SELECT '0001', 'JohnSmith' FROM DUAL UNION ALL
  SELECT '0002', 'JaneSmith' FROM DUAL;

两个输出：
| 卖家ID|卖方|月|月份名称| 2023 | 2022 | 2021 |
| --|--|--|--|--|--|--|
| 0001 |JohnSmith| 01 |一月| 900 | 1000 | 200 |
| 0001 |JohnSmith| 02 |二月| 800 | 500 | 100 |
| 0001 |JohnSmith| 03 |三月| 700 | 300 | 0 |
| 0002 |简·史密斯| 01 |一月| 9000 | 10000 | 2000 |
| 0002 |简·史密斯| 02 |二月| 8000 | 5000 | 1000 |
| 0002 |简·史密斯| 03 |三月| 7000 | 3000 | 999 |
或：

SELECT c.seller_id,
       MAX(s.name) as seller,
       TO_CHAR(c."DATE", 'MM') AS month,
       TO_CHAR(c."DATE", 'Month') AS month_name,
       SUM(
         CASE
         WHEN EXTRACT(YEAR FROM c."DATE") = EXTRACT(YEAR FROM SYSDATE)
         THEN c.s_total
         END
       ) AS current_year,
       SUM(
         CASE
         WHEN EXTRACT(YEAR FROM c."DATE") = EXTRACT(YEAR FROM SYSDATE) - 1
         THEN c.s_total
         END
       ) AS previous_year,
       SUM(
         CASE
         WHEN EXTRACT(YEAR FROM c."DATE") = EXTRACT(YEAR FROM SYSDATE) - 2
         THEN c.s_total
         END
       ) AS previous_year_2
FROM   sale_header c
       INNER JOIN saler s
       ON c.SELLER_ID=s.ID
WHERE  c."DATE" >= ADD_MONTHS(TRUNC(SYSDATE, 'YY'), -24)
AND    c."DATE" <  ADD_MONTHS(TRUNC(SYSDATE, 'YY'), +12)
GROUP BY
       c.seller_id,
       TO_CHAR(c."DATE", 'MM'),
       TO_CHAR(c."DATE", 'Month')

或

SELECT *
FROM   (
  SELECT c.seller_id,
         s.name as seller,
         TO_CHAR(c."DATE", 'MM') AS month,
         TO_CHAR(c."DATE", 'Month') AS month_name,
         EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM c."DATE") AS year_diff,
         c.s_total
  FROM   sale_header c
         INNER JOIN saler s
         ON c.SELLER_ID=s.ID
  WHERE  c."DATE" >= ADD_MONTHS(TRUNC(SYSDATE, 'YY'), -24)
  AND    c."DATE" <  ADD_MONTHS(TRUNC(SYSDATE, 'YY'), +12)
)
PIVOT (
  SUM(s_total)
  FOR year_diff IN (
    0 AS current_year,
    1 AS previous_year1,
    2 AS previous_year2
  )
)

两个输出：
| 卖家ID|卖方|月|月份名称|当前_年份|上一年|前一年2|
| --|--|--|--|--|--|--|
| 0001 |JohnSmith| 01 |一月| 900 | 1000 | 200 |
| 0001 |JohnSmith| 02 |二月| 800 | 500 | 100 |
| 0001 |JohnSmith| 03 |三月| 700 | 300 | 0 |
| 0002 |简·史密斯| 01 |一月| 9000 | 10000 | 2000 |
| 0002 |简·史密斯| 02 |二月| 8000 | 5000 | 1000 |
| 0002 |简·史密斯| 03 |三月| 7000 | 3000 | 999 |
fiddle

赞(0）回复(0）举报 2023-10-16

js4nwp542#

正如MT0所指出的，这是一个汇总问题。
最后，我提出了以下核心请求：

select c.SELLER_ID, r.NAME as SELLER,
         extract(year from c.DATE) as YEAR,
         extract(month from c.DATE) as MONTH,
         to_char(extract(month from c.DATE), 'Month') as MONTH_NAME,
        sum(c.S_TOTAL) as MONTHLY_TOTAL
    from SALE c,
         SELLER r
   where c.SELLER_ID=r.ID
     and extract(year from c.DATE) between extract(year from sysdate) -2 and extract(year from sysdate)
   group by SELLER_ID, r.NAME, extract(month from c.DATE), to_char(extract(month from c.DATE), 'Month') ,
         extract(year from c.DATE)
   order by c.SELLER_ID, to_char(c.DATE, 'YYYY-MM')

赞(0）回复(0）举报 2023-10-16

我来回答

Oracle中的代表性月销售额汇总

2条答案

相关问题

热门标签

最新问答