0

Let's say I have sales number of last 60 days. How would like to get sum of every last X days of everyday.

For example my graph will include

  • Sum of Day - 60 to 30
  • Sum of Day - 59 to 29
  • Sum of Day - 58 to 28
  • Sum of Day - 57 to 27

......

  • Sum of Day - 33 to 3
  • Sum of Day - 32 to 2
  • Sum of Day - 31 to 1

So that it will give me 4 results ( Day 8 to 4, Days 7 to 3, Day 6 to 2, Day 5 to 1 ). It will be sum of number_of_sales

CREATE TABLE sales (
  sales_id          int  PRIMARY KEY,
  sales_create_date timestamp,
  number_of_sales   int
);

INSERT INTO sales (sales_id, sales_create_date,number_of_sales) 
VALUES
  (0, '2015-05-02 05:30:40',100),
  (1, '2015-05-02 00:00:00',200),
  (2, '2015-05-03 00:00:00',300),
  (3, '2015-05-03 00:00:00',400),
  (4, '2015-05-05 00:00:00',500),
  (5, '2015-05-06 00:00:00',600),
  (6, '2015-05-07 00:00:00',700),
  (7, '2015-05-08 00:00:00',800),
  (8, '2015-05-08 00:00:00',900);

This is the final result that I need from this query enter image description here

In my real case, I will get sales of last 60 days, and it will return me 30 records but summing up previous 30 days every day. In example I gave you rather than analyzing 60 days, I analyzed 8 days, which will product me 4 entries. Plase don't spend time by getting the date of last entry. Just think the start date is today always.

  • 1
    Is this Postgres or Mysql? – Sam Jan 12 at 15:31
  • I just used a boilerplate to simplify and it was on it a default. If having ENGINE=InnoDB is more important than teaching someone who struggled to solve a problem, just close. @EvanCarroll I can just create another question by just removing Innodb.. – SNaRe Jan 12 at 16:41
  • 1
    @Sam yes it is postgresql sorry that at least to give a sqlfiddle I used a boilerplate. You can ignore it. Thank for understanding. – SNaRe Jan 12 at 16:42
  • Or edit the question and clarify, are you asking for PostgreSQL or MySQL. – Evan Carroll Jan 12 at 16:42
  • @EvanCarroll Edited. – SNaRe Jan 12 at 16:43
0

Here is what I think you want,

SELECT days.d::date, sum(sales.number_of_sales)
FROM (
        SELECT min(sales_create_date::date), max(sales_create_date::date)
        FROM sales
) AS minmax
CROSS JOIN LATERAL generate_series(minmax.min, minmax.max, '2 days')
        AS days(d)
LEFT OUTER JOIN sales
        ON sales_create_date >= days.d
        AND sales_create_date < (days.d + '2 days')
GROUP BY days.d;

Notice you have to put the window date twice in the above.

What we're doing is,

  1. Selecting min and max dates for the table
  2. Generating the start for the range on the window
  3. Joining back to sales with the range start and the end interval
  4. Grouping and selecting the count(*) in the window.

Result,

     d      | sum  
------------+------
 2015-05-02 | 1000
 2015-05-04 |  500
 2015-05-08 | 1700
 2015-05-06 | 1300
(4 rows)
  • Evan thank you. For me min max dates are not important. My table has 2 years of data. So that for me just last 4 days is important. It may simplify your SQL – SNaRe Jan 12 at 17:32
  • If you will edit accordingly can you please also ad sales_create_date as alias? db-fiddle.com/f/j2HJfdTkCVNKit79C7euSN/0 – SNaRe Jan 12 at 17:33
  • Also I have 9 entries purposefully since I won't have exact 8. For my case I will check all entries in last previous 30 days but the result will be only 30 results since it will be some of the values between dates. In this example I just wanted to make it easier and said every 4 days interval. – SNaRe Jan 12 at 17:38
  • no idea what you're talking about, feel free to post your data and desired results. – Evan Carroll Jan 12 at 17:47
  • Can you please check my edit on my Orginal post? I have a table that explains what I need. Thanks for your time. I should have done posted earlier. – SNaRe Jan 12 at 17:48
-1

I just learnt that what I want is moving average in statistics. So that I googled articles and found that there are nearly built in functions for these operations.

Something like

SELECT   
       SUM(ad.number_of_sales)
            OVER(ORDER BY ad.sales_create_date ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) AS avg_downloads
FROM sales ad  
;

I just drop the link of the article since it has a lot of goodies.

https://www.compose.com/articles/metrics-maven-calculating-a-moving-average-in-postgresql/

  • 2
    This is a sum, not an average. And it's for 5 rows (including the current row), not 4. And it's for rows, not days. If you don't have exactly one row for every date, this breaks. – Erwin Brandstetter Jan 13 at 0:24
  • @ErwinBrandstetter you are right, I have to group distinct dates into one and sum them up, so I will have only incremental dates, after that I can do as it is shown in the article. To make it 4 it states something like ROWS BETWEEN 1 FOLLOWING AND 30 FOLLOWING to be added to query – SNaRe Jan 13 at 6:13
  • It is over my knowledge actually, I tried to find the solution, at least we have a starting point. If you can help me with that I would really appricate that. – SNaRe Jan 13 at 6:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.