Uso delle window per calcolare statistiche

SQL Server intermedio

Ginger Grant

Instructor

Calcolare la deviazione standard

Calcola la deviazione standard per l'intera tabella o per ogni window
STDEV() calcola la deviazione standard

Deviazione standard sull'intera tabella

SELECT SalesPerson, SalesYear, CurrentQuota, 
       STDEV(CurrentQuota) 
       OVER () AS StandardDev, 
       ModifiedDate  AS ModDate
FROM SaleGoal

+------------+----------+------------+-----------------+----------+
|SalesPerson |SalesYear |CurrentQuota|StandardDev      | ModDate  |
+------------+----------+------------+-----------------+----------+
|Bob         |2011      |28000.00    |267841.370964233 |2011-04-16|
|Bob         |2011      |7000.00     |267841.370964233 |2011-07-17| 
|Bob         |2011      |91000.00    |267841.370964233 |2011-10-17|
|Bob         |2012      |140000.00   |267841.370964233 |2012-01-15|
|Bob         |2012      |70000.00    |267841.370964233 |2012-04-15|
...
+------------+----------+------------+-----------------+----------+

Deviazione standard per ogni partizione

SELECT SalesPerson, SalesYear, CurrentQuota,  
       STDEV(CurrentQuota) 
       OVER (PARTITION BY SalesYear ORDER BY SalesYear) AS StDev, 
       ModifiedDate AS ModDate
FROM SaleGoal

+------------+----------+------------+-------------+----------+
|SalesPerson |SalesYear |CurrentQuota|StDev        | ModDate  |
+------------+----------+------------+-------------+----------+
|Bob         |2011      |28000.00    |267841.54080 |2011-04-16|
|Bob         |2011      |7000.00     |267841.54080 |2011-07-17| 
|Mary        |2011      |91000.00    |267841.54080 |2011-04-16|
|Mary        |2011      |140000.00   |267841.54080 |2011-07-15|
|Bob         |2012      |70000.00    |246538.86248 |2012-01-15|
|Bob         |2012      |154000.00   |246538.86248 |2012-04-15|
|Bob         |2012      |107000.00   |246538.86248 |2012-07-16|
...
+------------+----------+------------+------------+-----------+

Calcolare la moda

La moda è il valore che compare più spesso nei dati
Per calcolare la moda:
- Crea una CTE con un conteggio ordinato dei valori usando ROW_NUMBER
- Scrivi una query che usa la CTE per scegliere il valore con il numero di riga più alto

Calcolare la moda in T-SQL (I)

WITH QuotaCount AS (
SELECT SalesPerson, SalesYear, CurrentQuota,  
       ROW_NUMBER() 
       OVER (PARTITION BY CurrentQuota ORDER BY CurrentQuota) AS QuotaList
FROM SaleGoal 
)
SELECT * FROM QuotaCount

+------------+----------+------------+-------------+
|SalesPerson |SalesYear |CurrentQuota|QuotaList    | 
+------------+----------+------------+-------------+
|Bob         |2011      |7000.00     |1            |
|Bob         |2011      |28000.00    |1            | 
|Bob         |2011      |70000.00    |1            |
|Bob         |2012      |70000.00    |2            |
|Mary        |2012      |73000.00    |1            |
...
+------------+----------+------------+-------------+

Nota che ci sono due valori pari a 70.000,00

Calcolare la moda in T-SQL (II)

WITH QuotaCount AS (
SELECT SalesPerson, SalesYear, CurrentQuota, 
       ROW_NUMBER() 
       OVER (PARTITION BY CurrentQuota ORDER BY CurrentQuota) AS QuotaList
FROM SaleGoal 
)

SELECT CurrentQuota, QuotaList AS Mode 
FROM QuotaCount 
WHERE QuotaList IN (SELECT  MAX(QuotaList) FROM QuotaCount)

+------------+----------+
|CurrentQuota|Mode      | 
+------------+----------+
|70000.00    |2         |
+------------+----------+

Esercitiamoci!

SQL Server intermedio