I have a table that looks like this:
+--------+----------+--------+------------+-------+
| ID | CHANNEL | VENDOR | num_PERIOD | SALES |
+--------+----------+--------+------------+-------+
| 000001 | Business | Shop | 1 | 40 |
| 000001 | Business | Shop | 2 | 60 |
| 000001 | Business | Shop | 3 | NULL |
+--------+----------+--------+------------+-------+
With many combinations of ID, CHANNEL and VENDOR, and sales records for each of them over time (num_PERIOD).
The idea is to obtain a new column which returns the number of NULLS in SALES column, but in the first 111 registers according to num_PERIOD column.
I have been trying something like this:
SELECT ID,
CHANNEL,
VENDOR,
sum(CASE
WHEN SALES IS NULL THEN 1
ELSE 0
END) OVER (PARTITION BY ID,
CHANNEL,
VENDOR
ORDER BY num_PERIOD ROWS BETWEEN UNBOUNDED PRECEDING AND 111 FOLLOWING) AS NULL_SALES_SET
FROM TABLE
GROUP BY ID,
CHANNEL,
VENDOR
But I'm not obtaining what I'm looking for.
So to obtain a table simillar to:
+--------+--------------+--------+----------------+
| ID | CHANNEL | VENDOR | NULL_SALES_SET |
+--------+--------------+--------+----------------+
| 000001 | Business | Shop | 1 |
| 000002 | Business | Market | 0 |
| 000002 | Non Business | Shop | 3 |
+--------+--------------+--------+----------------+
The difficulty comes when selecting these first 111 rows per ID, CHANNEL AND VENDOR ordered by num_PERIOD.