SQL Retrieve the records of previous month-CodePudding

Hi all, i have this dataset which is the sales of some products. Date field is the date of sale, Brand the Brand of the product, Channel is the Channel that the product is sold and Sales is the price that the product is sold.

I need to show for every month, channel and brand the total price (Sales) and the total price of previous month in sql. I can retrieve the total price of the current month with a query like this

SELECT TO_CHAR(DATE,'MONTH'), BRAND CHANNEL, SUM(SALES) FROM SAELS
GROUP BY TO_CHAR(DATE,'MONTH'), BRAND CHANNEL

But how i can take the total sales of the previous month?

CodePudding user response：

Generate a calendar so that you do not skip missing months and then use the LAG analytic function:

WITH calendar (month) AS (
  SELECT ADD_MONTHS(min_month, LEVEL - 1)
  FROM   (
    SELECT MIN(TRUNC("DATE", 'MM')) AS min_month,
           MAX(TRUNC("DATE", 'MM')) AS max_month
    FROM   sales
  )
  CONNECT BY
         LEVEL - 1 <= MONTHS_BETWEEN(max_month, min_month)
)
SELECT c.month,
       s.BRAND,
       s.CHANNEL,
       COALESCE(SUM(s.SALES), 0) AS month_sales,
       LAG(COALESCE(SUM(s.SALES), 0), 1, 0)
         OVER (PARTITION BY brand, channel ORDER BY c.month)
         AS prev_month_sales
FROM   calendar c
       LEFT OUTER JOIN SALES s
       PARTITION BY (s.brand, s.channel)
       ON (c.month = TRUNC(s."DATE",'MM'))
GROUP BY
       c.month,
       BRAND,
       CHANNEL

Which, for the sample data:

CREATE TABLE sales ("DATE", brand, channel, sales) AS
SELECT DATE '2021-01-01', 'LEF A', 1, 100 FROM DUAL UNION ALL
SELECT DATE '2021-01-02', 'LEF B', 2, 200 FROM DUAL UNION ALL
SELECT DATE '2021-02-01', 'LEF B', 1,  23 FROM DUAL UNION ALL
SELECT DATE '2021-02-02', 'LEF A', 1,  48 FROM DUAL UNION ALL
SELECT DATE '2021-03-01', 'LEF B', 2, 281 FROM DUAL UNION ALL
SELECT DATE '2021-03-02', 'LEF B', 1, 940 FROM DUAL;

(Note: It is bad practice to use reserved words for identifiers. You should name your column something other than DATE.)

Outputs:

MONTH BRAND CHANNEL MONTH_SALES PREV_MONTH_SALES

2021-01-01 00:00:00 LEF A 1 100 0

2021-02-01 00:00:00 LEF A 1 48 100

2021-03-01 00:00:00 LEF A 1 0 48

2021-01-01 00:00:00 LEF B 1 0 0

2021-02-01 00:00:00 LEF B 1 23 0

2021-03-01 00:00:00 LEF B 1 940 23

2021-01-01 00:00:00 LEF B 2 200 0

2021-02-01 00:00:00 LEF B 2 0 200

2021-03-01 00:00:00 LEF B 2 281 0

MONTH	BRAND	CHANNEL	MONTH_SALES	PREV_MONTH_SALES
2021-01-01 00:00:00	LEF A	1	100	0
2021-02-01 00:00:00	LEF A	1	48	100
2021-03-01 00:00:00	LEF A	1	0	48
2021-01-01 00:00:00	LEF B	1	0	0
2021-02-01 00:00:00	LEF B	1	23	0
2021-03-01 00:00:00	LEF B	1	940	23
2021-01-01 00:00:00	LEF B	2	200	0
2021-02-01 00:00:00	LEF B	2	0	200
2021-03-01 00:00:00	LEF B	2	281	0

db<>fiddle here

CodePudding user response：

What I do in such situations is create adjacent month numbers by calculating (year * 12) month. The rest is a simple self join.

WITH monthly AS
(
  SELECT
    brand, channel, TRUNC(date, 'mm') AS month, SUM(sales) AS sum_sales,
    MAX(EXTRACT(YEAR FROM date) * 12   EXTRACT(MONTH FROM date)) AS month_num
  FROM sales
  GROUP BY brand, channel, TRUNC(date, 'mm')
)
SELECT m.month, m.brand, m.channel, m.sum_sales, prev.sum_sales as prev_month_sales
FROM monthly m
LEFT JOIN monthly prev ON prev.brand = m.brand
                      AND prev.channel = m.channel
                      AND prev.month_num = m.month_num - 1
ORDER BY m.month, m.brand, m.channel;

(This would probably work without my month numbers, though. As I truncate the dates to the first day of their month, I could probybly just join on prev.month = m.month - interval '1' month. But I don't like adding and subtracting months, because a month is not a precise time span unit, so I rather go with my numbers.)