Utilizarea ferestrelor pentru calcularea statisticilor

SQL Server intermediar

Ginger Grant

Instructor

Calcularea abaterii standard

Calculați abaterea standard pentru întregul tabel sau pentru fiecare fereastră
STDEV() calculează abaterea standard

Calcularea abaterii standard pentru întregul tabel

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|
...
+------------+----------+------------+-----------------+----------+

Calcularea abaterii standard pentru fiecare partiție

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|
...
+------------+----------+------------+------------+-----------+

Calcularea modului

Modul este valoarea care apare cel mai frecvent în date
Pentru a calcula modul:
- Creați un CTE cu un număr ordonat de valori folosind ROW_NUMBER
- Scrieți o interogare care utilizează CTE-ul pentru a selecta valoarea cu numărul de rând cel mai mare

Calcularea modului în 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            |
...
+------------+----------+------------+-------------+

Observați că există două valori pentru 70.000,00

Calcularea modului în 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         |
+------------+----------+

Să exersăm!

SQL Server intermediar