Calculating standard deviation when some dates are missing

Question

I have the input data: I want to calculate the standard deviation such that the missing dates values of item_demand should be taken as 0.

Input Data:

	checkout_date	item_demand
0	2022-08-02	1
1	2022-08-05	2
2	2022-08-07	1
3	2022-08-08	1
4	2022-08-09	1
5	2022-08-12	2

I used inbuilt function :

cast(stddev(item_demand) as dec(14,2)) deviation

But since some of my dates are missing the inbuilt function won't give a proper result; I want to take into account the missing dates (with item_demand as 0 ) also, and then I want the standard deviation.
Please suggest how to achieve this. I'm new to SQL

Answer 1

You'll have to explicitely compute and include the missing dates,
because the RDBMS can't decide how many 0s you'll want.

For example, do you want dates starting with the first checked out item, or with the month start?

Then technically it will be quite simple, just coalescing item_demand to 0 for dates in this list having no corresponding row in your table.

Here a PostgreSQL example (with the assumption you want dates between the minimal and maximal ones seen in your table):

with alldates as
(
  select generate_series(min(checkout_date), max(checkout_date), interval '1 day') d
  from t
)
select cast(stddev(coalesce(item_demand, 0)) as dec(14,2)) deviation
from alldates d left join t on t.checkout_date = d.d;

(see it in a fiddle)

Or with MySQL:

with recursive alldates as
(
  select min(checkout_date) d from t
  union
  select d + 1 from alldates where d < (select max(checkout_date) from t)
)
select cast(stddev(coalesce(item_demand, 0)) as dec(14,2)) deviation
from alldates d left join t on t.checkout_date = d.d;

(and its fiddle)