Sélectionnez N lignes dans les fonctions d'agrégation SQL Server
J'ai une table qui ressemble à ceci:
+--------+----------+--------+------------+-------+ | ID | CHANNEL | VENDOR | num_PERIOD | SALES | +--------+----------+--------+------------+-------+ | 000001 | Business | Shop | 1 | 40 | | 000001 | Business | Shop | 2 | 60 | | 000001 | Business | Shop | 3 | NULL | +--------+----------+--------+------------+-------+ Avec de nombreuses combinaisons d' ID , de CHANNEL et de VENDOR , et d'loggings de sales pour chacun d'eux au fil du time ( num_PERIOD ).
L'idée est d'get une nouvelle colonne qui renvoie le nombre de NULLS dans la colonne SALES , mais dans les 111 premiers registres selon la colonne num_PERIOD .
J'ai essayé quelque chose comme ça:
- Diviser deux lignes sur la partition
- TSQL Partition par avec ordre par
- Comment filterr sur ROW_NUMBER ()
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
Mais je n'obtiens pas ce que je cherche.
Donc pour get une table simillar à:
+--------+--------------+--------+----------------+ | ID | CHANNEL | VENDOR | NULL_SALES_SET | +--------+--------------+--------+----------------+ | 000001 | Business | Shop | 1 | | 000002 | Business | Market | 0 | | 000002 | Non Business | Shop | 3 | +--------+--------------+--------+----------------+
La difficulté vient lors de la sélection de ces 111 premières lignes par ID , CHANNEL AND VENDOR commandées par num_PERIOD .
Avez-vous besoin d'utiliser la fonction de fenêtrage?
SELECT ID , CHANNEL , VENDOR , NULL_SALES_SET = SUM(CASE WHEN SALES IS NULL THEN 1 ELSE 0 END) FROM Table WHERE num_PERIOD <= 111 GROUP BY ID, CHANNEL, VENDOR
Ou searchz-vous les 111 num_PERIOD valeurs de num_PERIOD permettant des espaces dans la colonne num_PERIOD ?
SELECT t.ID , t.CHANNEL , t.VENDOR , NULL_SALES_SET = SUM(CASE WHEN t.SALES IS NULL THEN 1 ELSE 0 END) FROM Table t INNER JOIN ( SELECT i.ID , i.CHANNEL , i.VENDOR , i.num_PERIOD , rowNum = ROW_NUMBER(PARTITION BY i.ID, i.CHANNEL, i.VENDOR ORDER BY i.num_PERIOD) FROM Table i ) l ON t.ID = l.ID AND t.CHANNEL = l.CHANNEL AND t.VENDOR = l.VENDOR AND t.num_PERIOD = l.num_PERIOD WHERE l.rowNum <= 111 GROUP BY ID, CHANNEL, VENDOR
Edit: Je ne sais pas comment je l'ai négligé, mais il est nécessaire de JOIN sur la colonne num_PERIOD.
Modifier: Ajouter le nombre de num_PERIOD distinct par ID, Canal, Fournisseur sans affecter le NULL_SALES_SET
SELECT t.ID , t.CHANNEL , t.VENDOR -- Counts the NULL Sales when the num_PERIOD is in the -- first 111 num_PERIODs , NULL_SALES_SET = SUM(CASE WHEN l.rowNum IS NOT NULL AND t.SALES IS NULL THEN 1 ELSE 0 END) -- Counts the distinct num_PERIOD values , PERIOD_COUNT = COUNT(DISTINCT t.num_PERIOD) FROM Table t LEFT OUTER JOIN ( SELECT i.ID , i.CHANNEL , i.VENDOR , i.num_PERIOD , rowNum = ROW_NUMBER(PARTITION BY i.ID, i.CHANNEL, i.VENDOR ORDER BY i.num_PERIOD) FROM Table i ) l ON t.ID = l.ID AND t.CHANNEL = l.CHANNEL AND t.VENDOR = l.VENDOR AND t.num_PERIOD = l.num_PERIOD AND l.rowNum <= 111 GROUP BY ID, CHANNEL, VENDOR
Utilisez une expression CTE (Common Table Expression) avec la fonction fenêtrée ROW_NUMBER et vous devez définir:
;WITH MyCTE AS ( SELECT id, channel, vendor, sales, ROW_NUMBER() OVER (PARTITION BY id, channel, vendor ORDER BY num_period) AS row_num FROM MyTable ) SELECT id, channel, vendor, SUM(CASE WHEN sales IS NULL THEN 1 ELSE 0 END) AS null_sales_set FROM MyCTE WHERE row_num <= 111 GROUP BY id, channel, vendor