MySQL: Create running sum for all dates in range?-CodePudding

I have a table Service, that stores records of sales and referral types that led to the sale. I need to know the total number of sales that resulted from a given referral type over a range of dates. The relevant data in Service looks like the following:

 ------ ------- ------------ 
| uuid | sr_id | s_saledate |
 ------ ------- ------------ 
|      |     1 | 2020-01-01 |
|      |     1 | 2020-01-01 |
|      |     2 | 2021-01-01 |
|      |     2 | 2021-01-01 |
|      |     1 | 2021-01-01 |
 ------ ------- ------------

I want to count the number of sales for each referral type (sr_id) in a given date range.

If my date range is 2020-01-01 thru 2021-01-01, my output should be something like:


 ------ ------- ------------ ---------------------- 
| uuid | sr_id | date       | num_sales_as_of_date
 ------ ------- ----------------------------------- 
|      |     1 | 2020-01-01 | 2                    |
|      |     1 | 2020-01-02 | 2                    |
|      |     1 | 2020-01-03 | 2                    |
........................................................ < many rows for days in range 
             1   2021-01-01 | 3
|      |     2 | 2020-01-01 | 0                    |
|      |     2 | 2020-01-02 | 0                    |
........................................................ < many rows for days in range 
|      |     2 | 2020-01-01 | 2                    |
 ------ ------- -----------------------------------

There should be a row for each referral type on each date in the range.

Right now my query looks like:

SELECT s.sr_id,
       s.s_saledate               AS date,
       Count(s.uuid)
         OVER (
           partition BY s.sr_id
           ORDER BY s.s_saledate) AS num_sales_as_of_date
FROM   Service s

How do I get the running sum for each referral type on days that had no Service with that particular referral type id? *** EDIT FOR CLARIFICATION***

For example, in the first table I give there is no Service row in the Service table with sr_id = '1' AND s_saledate === "2020-01-02". There were two rows from prior days where sr_id = '1'. (2020-01-01). My output row for "2020-01-02" is:

sr_id  date       num_sales_as_of_date 
  1 | 2020-01-02 | 2                  |

CodePudding user response：

You need to left join your Services table from a table with all the dates in the range and a table with all the referral types, so that you get a row with every combination of date and referral type:

WITH RECURSIVE dates AS (
    SELECT date('2020-01-01') AS date
    UNION ALL
    SELECT dates.date   INTERVAL 1 DAY
    FROM dates
    WHERE dates.date <= '2020-01-05'
)
SELECT ServiceReferral.sr_id,
       dates.date,
       Count(s.uuid)
         OVER (
           partition BY ServiceReferral.sr_id
           ORDER BY dates.date) AS num_sales_as_of_date
FROM dates
CROSS JOIN ServiceReferral
LEFT JOIN Service s ON s.s_saledate=dates.date AND s.sr_id=ServiceReferral.sr_id

fiddle

If you do this a lot, it may be more convenient to create an actual table dates with all the dates from 0000-01-01 to 9999-12-31 and use that instead (selecting dates in the desired range in the where clause).