Użycie okien do obliczania statystyk

SQL Server średnio zaawansowany

Ginger Grant

Instructor

Obliczanie odchylenia standardowego

Oblicza odchylenie standardowe dla całej tabeli lub każdego okna
STDEV() oblicza odchylenie standardowe

Obliczanie odchylenia standardowego dla całej tabeli

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

Obliczanie odchylenia standardowego dla każdej partycji

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

Obliczanie dominanty

Dominanta to wartość występująca najczęściej w danych
Obliczanie dominanty:
- Utwórz CTE z uporządkowanym licznikiem wartości przy użyciu ROW_NUMBER
- Napisz zapytanie używające CTE, które wybiera wartość z najwyższym numerem wiersza

Obliczanie dominanty w 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            |
...
+------------+----------+------------+-------------+

Widać dwie wartości dla 70 000,00

Obliczanie dominanty w 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         |
+------------+----------+

Czas na ćwiczenia!

SQL Server średnio zaawansowany