Date trunc and partition by subject-CodePudding

I have a db with column name containing different companies names, and column date, where each company has all days of the year ( so date= 1/3/2007 appear for name=x and also for name=y).

I need to calculate monthly profit separately for each company.

I am able to do this for a single company:

    WITH BS AS (
    SELECT DISTINCT ON(date_trunc('month', date)) date_trunc('month', date), date_num, name, profit as c
    FROM history
    WHERE name IN ('X', 'Y') //companies
    ),
    LG AS (
    SELECT date_num, name, c, LAG(c,1) OVER (PARTITION BY name ORDER BY date_num) o
    FROM BS 
    )

    SELECT date_num, name, c, o,  100 * ( (c - o)/o )  as trends
    FROM LG
    ORDER BY date_num  DESC

This works for a single company, but if I have multiple (X Y) how can I separate the DISTINCT date selection for each name ?

desired output (assuming each name as all possible dates of the year):

name  date      trend

X     1.2.2020.   3
X     1.3.2020.   4
X     1.4.2020.   7
Y     1.2.2020.   9
Y     1.3.2020.   2
Y     1.4.2020.   5

Original data :

name  date      profit  date_num

X     1.2.2020.   2     1638316800 (EPOCH DATES)
X     2.2.2020.   1     ....
X     3.2.2020.   6     ..
Y     1.2.2020.   5
Y     2.2.2020.   4
Y     3.2.2020.   2

CodePudding user response：

Using DISTINCT ON requires a proper ORDER BY to return the correct rows.

WITH BS AS (
   SELECT DISTINCT ON(date_trunc('month', date), name) date, name, date_num, profit c
   FROM history
   WHERE name IN ('X', 'Y') 
   ORDER BY date_trunc('month', date), name, date_num
), LG AS (
      SELECT date_num, name, c, LAG(c,1) OVER(PARTITION BY name ORDER BY date_num) o
      FROM BS 
)
SELECT date_num, name, c, o,  100.0 * ( (c - o)/o )  as trends
FROM LG
ORDER BY date_num DESC

db<>fiddle