Welcome toVigges Developer Community-Open, Learning,Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.0k views
in Technique[技术] by (71.8m points)

sql - PostgreSQL counting from a special date

i have the following code,

SELECT      
    years_month_count.day_date,
    years_month_count.year_date,
    years_month_count.month_date,
    years_month_count.no_of_customers_day,
    sum(years_month_count.no_of_customers_day) OVER (PARTITION BY year_date ORDER BY day_date) AS no_of_customers_ytd
FROM (
    SELECT 
        DATE(date) as day_date,
        DATE_PART('year',date) as year_date,
        DATE_PART('month',date) as month_date,
        count(prepare_first_buyer.person_id) as no_of_customers_day
    FROM (
        SELECT 
            DATE(bestelldatum),
            person_id,
            ROW_NUMBER() OVER (PARTITION BY person_id ORDER BY person_id)
        FROM ani.bestellung
    ) prepare_first_buyer
    WHERE row_number=1
    GROUP BY DATE(date), DATE_PART('year',date),DATE_PART('month',date)
    ORDER BY DATE(date), DATE_PART('year',date),DATE_PART('month',date)
) years_month_count

the Output looks like this:

day_date year_date month_date no_of_customers_day no_of_Customers_ytd
2017-04-04 2017 4 6 6
2017-04-05 2017 4 4 10
... ... ... ... ...
... ... ... ... ...

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

step-by-step demo:db<>fiddle

SELECT
    *,
    SUM(value) OVER (PARTITION BY                             -- 4
        date_part('year',                                     -- 3
            the_date - interval '5 months'                    -- 2
        )     
    )
FROM t
WHERE date_part('month', the_date)::int NOT BETWEEN 4 AND 5   -- 1
  1. Filter all dates you are not requiring. In your example all dates with months 4 and 5
  2. Shift your date range start to the beginning of the year. In your example you have to shift: year-06-01 to year-01-01, so you need to subtract 5 months. Because your date range never exceeds a year, all your relevant data now has the same year, which makes a great group criterion
  3. Extract the year part to use it as group/partition criterion
  4. Do your calculation on this criterion

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to Vigges Developer Community for programmer and developer-Open, Learning and Share
...