When you get a new dataset, one of the first things you want to do is find your summary statistics. In this article we'll explain how it's done.
When you get a new dataset, one of the first things you want to do is find your summary statistics. These essential numbers tell you the basics about your data so you start off your analysis on an informed foot.
The main summary statistics are:
select * from PUBLIC.LONDON_WEATHER| DATE | TEMP | WEATHER | PRECIPITATION | VISIBILITY | WINDSPEED |
|---|---|---|---|---|---|
| 2018-04-11 | 48.5 | fog | 0.2 | 2 | 5.7 |
| 2016-10-31 | 54.7 | fog | 0.01 | 2.5 | 2.1 |
| 2018-11-23 | 44.2 | rain | 0.01 | 2.5 | 5.5 |
| 2018-01-09 | 41.6 | rain | null | 3 | 5.5 |
| 2017-01-26 | 31 | sunny | 0 | 3 | 7.7 |
| 2017-02-13 | 42.8 | sunny | 0 | 3 | 11.9 |
| 2016-12-31 | 42.8 | fog | null | 3.5 | 5.7 |
| 2018-05-31 | 65.7 | fog | 0 | 3.5 | 5.1 |
| 2017-01-27 | 37.8 | sunny | 0 | 3.5 | 6.1 |
| 2018-03-02 | 30.6 | rain | 0 | 3.5 | 14.2 |
| 2019-02-16 | 47.4 | sunny | 0.01 | 3.5 | 6.3 |
| 2018-02-19 | 47.7 | rain | 0.02 | 3.5 | 5.4 |
| 2017-01-31 | 45 | rain | 0.03 | 3.5 | 7.1 |
| 2019-12-04 | 38.9 | fog | 0 | 4 | 2.3 |
| 2017-11-02 | 47.5 | fog | 0 | 4 | 2.7 |
| 2017-03-11 | 54.2 | sunny | 0 | 4 | 5.6 |
| 2018-05-29 | 59.5 | fog | 0 | 4 | 6.4 |
| 2017-03-16 | 48.7 | fog | 0 | 4 | 7.8 |
| 2018-03-24 | 46.2 | rain | 0.09 | 4 | 4.1 |
| 2016-12-28 | 36 | fog | 0.01 | 4 | 2.9 |
| 2019-11-09 | 40.4 | fog | 0.01 | 4 | 6 |
| 2018-05-30 | 60 | rain | 0.42 | 4 | 3.3 |
| 2018-04-14 | 55.2 | sunny | 0 | 4.5 | 4.5 |
| 2019-12-28 | 46.4 | fog | 0 | 4.5 | 5.7 |
| 2017-02-06 | 39.2 | fog | 0 | 4.5 | 5.1 |
| 2019-04-02 | 42.6 | rain | 0 | 4.5 | 5.8 |
| 2019-02-28 | 48.8 | rain | 0 | 4.5 | 8.6 |
| 2018-03-04 | 43 | rain | 0.19 | 4.5 | 7 |
| 2017-10-18 | 56.1 | rain | 0.01 | 4.5 | 4.3 |
| 2016-12-14 | 48.8 | fog | 0.01 | 4.5 | 4.9 |
| 2018-11-01 | 48 | fog | 0.16 | 4.5 | 5.3 |
| 2016-11-21 | 47.8 | rain | 0.4 | 4.5 | 8.8 |
| 2017-08-21 | 62.1 | rain | 0.26 | 4.5 | 4.9 |
| 2017-12-10 | 34.9 | rain | 0.21 | 4.5 | 6.1 |
| 2016-06-04 | 59.9 | sunny | 0 | 5 | 4.1 |
| 2019-11-08 | 40.7 | fog | 0 | 5 | 3.7 |
| 2018-10-05 | 60.5 | sunny | 0 | 5 | 4.3 |
| 2017-02-14 | 44.6 | sunny | 0 | 5 | 6.9 |
| 2019-04-30 | 53.5 | sunny | 0 | 5 | 4.3 |
| 2018-10-06 | 50.1 | fog | 0 | 5 | 6.8 |
| 2019-02-17 | 49 | fog | 0 | 5 | 8 |
| 2018-11-28 | 53.3 | rain | 0.27 | 5 | 11.7 |
| 2016-04-11 | 52.5 | rain | 0.05 | 5 | 6 |
| 2016-12-10 | 52.8 | rain | 0.03 | 5 | 8.4 |
| 2018-02-20 | 47.3 | rain | 0.06 | 5 | 7.8 |
| 2016-01-17 | 36.3 | rain | 0.07 | 5 | 6.4 |
| 2017-12-02 | 40.2 | rain | null | 5.5 | 4.8 |
| 2016-09-22 | 61.4 | rain | null | 5.5 | 6.6 |
| 2018-10-20 | 53.1 | sunny | 0 | 5.5 | 4 |
| 2019-01-11 | 45.5 | rain | 0 | 5.5 | 4.4 |
| 2017-05-07 | 54.5 | sunny | 0 | 5.5 | 5.4 |
| 2018-03-09 | 43.6 | rain | 0 | 5.5 | 5.8 |
| 2019-04-06 | 47.4 | fog | 0 | 5.5 | 7.6 |
| 2018-02-28 | 24.6 | snow | 0 | 5.5 | 8.9 |
| 2018-03-10 | 51.5 | rain | 0.25 | 5.5 | 8.1 |
| 2018-05-25 | 59.5 | rain | 0.09 | 5.5 | 3.3 |
| 2019-06-25 | 67.9 | rain | 0.11 | 5.5 | 4.1 |
| 2019-06-13 | 54.8 | rain | 0.57 | 5.5 | 10.5 |
| 2018-04-02 | 46.9 | rain | 0.46 | 5.5 | 9.3 |
| 2018-12-25 | 41.5 | sunny | 0.01 | 5.5 | 3.4 |
| 2017-04-04 | 53.1 | rain | 0.01 | 5.5 | 4.8 |
| 2018-07-29 | 64.6 | rain | 0.01 | 5.5 | 13 |
| 2019-02-08 | 48.6 | rain | 0.01 | 5.5 | 15.5 |
| 2019-01-23 | 34.8 | fog | 0.16 | 5.5 | 3.9 |
| 2018-05-27 | 69.3 | rain | 0.43 | 5.5 | 5.5 |
| 2018-04-22 | 61.7 | rain | 0.43 | 5.5 | 9.1 |
| 2019-12-20 | 46.8 | rain | 0.43 | 5.5 | 8.2 |
| 2016-06-09 | 62.7 | sunny | 0.05 | 5.5 | 4.9 |
| 2016-04-15 | 50 | rain | 0.1 | 5.5 | 4.2 |
| 2018-09-12 | 56.4 | rain | 0.13 | 5.5 | 3.8 |
| 2017-12-07 | 48 | rain | 0.12 | 5.5 | 12.7 |
| 2019-11-16 | 43.1 | rain | 0.07 | 5.5 | 4 |
| 2017-09-19 | 53.7 | rain | 0.14 | 5.5 | 4.3 |
| 2016-11-03 | 43.5 | rain | null | 6 | 5 |
| 2016-04-02 | 50.3 | rain | null | 6 | 7.6 |
| 2016-05-17 | 55.3 | rain | null | 6 | 7.7 |
| 2019-02-26 | 50.7 | sunny | 0 | 6 | 3 |
| 2018-11-26 | 43.1 | rain | 0 | 6 | 4.8 |
| 2016-12-01 | 35 | sunny | 0 | 6 | 4.1 |
| 2019-11-18 | 43.6 | rain | 0 | 6 | 4.6 |
| 2019-04-23 | 60.4 | sunny | 0 | 6 | 4.8 |
| 2019-08-25 | 76.3 | fog | 0 | 6 | 5.1 |
| 2017-03-10 | 49.1 | rain | 0 | 6 | 6.1 |
| 2018-06-02 | 65.4 | sunny | 0 | 6 | 7.8 |
| 2019-01-29 | 35.4 | rain | 0 | 6 | 5 |
| 2018-10-25 | 49.6 | sunny | 0 | 6 | 5.4 |
| 2018-11-14 | 52.1 | rain | 0 | 6 | 7.2 |
| 2018-02-22 | 37.7 | rain | 0 | 6 | 7.5 |
| 2017-04-03 | 51.5 | fog | 0 | 6 | 7 |
| 2018-05-09 | 57.1 | sunny | 0 | 6 | 7.2 |
| 2018-03-21 | 41.5 | sunny | 0 | 6 | 7.3 |
| 2016-04-01 | 45.9 | sunny | 0 | 6 | 7.7 |
| 2016-04-21 | 51.1 | sunny | 0 | 6 | 9.5 |
| 2019-01-16 | 45.6 | rain | 0 | 6 | 9.8 |
| 2018-09-11 | 66.6 | rain | 0 | 6 | 11.6 |
| 2018-11-09 | 50.9 | rain | 0 | 6 | 11.3 |
| 2016-09-05 | 64.6 | rain | 0.09 | 6 | 6 |
| 2017-10-13 | 61.4 | sunny | 0.09 | 6 | 10.5 |
| 2018-04-03 | 51.8 | rain | 0.18 | 6 | 10.3 |
| 2019-06-08 | 58.4 | rain | 0.33 | 6 | 15.3 |
| COL | MIN | 25th PERCENTILE | MEDIAN | 75th PERCENTILE | MAX | AVG | MODE | STD_DEV |
|---|---|---|---|---|---|---|---|---|
| TEMP | 24.6 | 45.675 | 52.75 | 61.8 | 83.4 | 53.512741047 | 57.7 | 10.211334572 |
| PRECIPITATION | 0 | 0 | 0 | 0.07 | 1.44 | 0.06960468521 | 0 | 0.1466155086 |
| VISIBILITY | 0.2 | 5.8 | 6.4 | 6.7 | 14.8 | 6.105027548 | 6.7 | 1.080844202 |
| WINDSPEED | 1.8 | 5.2 | 7.2 | 9.5 | 22.1 | 7.690013774 | 4.8 | 3.228622125 |
| COL | MIN | MAX | MODE | UNIQUE_VALUES |
|---|---|---|---|---|
| DATE | 2016-01-01 | 2019-12-31 | 2018-04-11 | 1452 |
| COL | MIN | MAX | MODE | UNIQUE_VALUES |
|---|---|---|---|---|
| WEATHER | fog | thunderstorm | rain | 5 |
Correlation matrices let you quickly see the correlations between all the different variables.
To create them, we will make use of the CORR
| COL_1 | COL_2 | CORR |
|---|---|---|
| TEMP | PRECIPITATION | -0.05288072854 |
| TEMP | VISIBILITY | 0.3310294722 |
| TEMP | WINDSPEED | 0.03470707836 |
| PRECIPITATION | VISIBILITY | -0.08879966527 |
| PRECIPITATION | WINDSPEED | 0.131898554 |
| VISIBILITY | WINDSPEED | 0.2166210513 |
| PRECIPITATION | TEMP | -0.05288072854 |
| VISIBILITY | PRECIPITATION | -0.08879966527 |
| WINDSPEED | TEMP | 0.03470707836 |
| VISIBILITY | TEMP | 0.3310294722 |
| WINDSPEED | PRECIPITATION | 0.131898554 |
| WINDSPEED | VISIBILITY | 0.2166210513 |
We can see the highest correlations are between temperature and visibility
Identifying outliers, or points that lie at the most extreme ends of your dataset, is a crucial to the rest of your analysis. Early identification, and potential removal of outliers makes for more accurate predictions and a better understanding of your data.
There are many ways to define outliers, but for this example, we'll use Tukey's fences:
SELECT * FROM (
SELECT
'TEMP' COL,
MIN(TEMP) MIN,
PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY TEMP) "25th PERCENTILE",
MEDIAN(TEMP) MEDIAN,
PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY TEMP) "75th PERCENTILE",
MAX(TEMP) MAX,
AVG(TEMP) AVG,
MODE(TEMP) MODE,
STDDEV(TEMP) STD_DEV
FROM PUBLIC.LONDON_WEATHER
GROUP BY COL)
UNION ALL (
SELECT
'PRECIPITATION' COL,
MIN(PRECIPITATION) MIN,
PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY PRECIPITATION) "25th PERCENTILE",
MEDIAN(PRECIPITATION) MEDIAN,
PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY PRECIPITATION) "75th PERCENTILE",
MAX(PRECIPITATION) MAX,
AVG(PRECIPITATION) AVG,
MODE(PRECIPITATION) MODE,
STDDEV(PRECIPITATION) STD_DEV
FROM PUBLIC.LONDON_WEATHER
GROUP BY COL
)
UNION ALL (
SELECT
'VISIBILITY' COL,
MIN(VISIBILITY) MIN,
PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY VISIBILITY) "25th PERCENTILE",
MEDIAN(VISIBILITY) MEDIAN,
PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY VISIBILITY) "75th PERCENTILE",
MAX(VISIBILITY) MAX,
AVG(VISIBILITY) AVG,
MODE(VISIBILITY) MODE,
STDDEV(VISIBILITY) STD_DEV
FROM PUBLIC.LONDON_WEATHER
GROUP BY COL
)
UNION ALL (
SELECT
'WINDSPEED' COL,
MIN(WINDSPEED) MIN,
PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY WINDSPEED) "25th PERCENTILE",
MEDIAN(WINDSPEED) MEDIAN,
PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY WINDSPEED) "75th PERCENTILE",
MAX(WINDSPEED) MAX,
AVG(WINDSPEED) AVG,
MODE(WINDSPEED) MODE,
STDDEV(WINDSPEED) STD_DEV
FROM PUBLIC.LONDON_WEATHER
GROUP BY COL
)A point is an outlier if it lies outside the range [Q1 - k(Q3-Q1) , Q3 + k(Q3-Q1)] where k is either 1.5 or 3 for identifying particularly 'far out' data.
We can use PERCENTILE_CONT to find our 25th and 75th percentiles:
SELECT
'DATE' COL,
MIN(DATE) MIN,
MAX(DATE) MAX,
MODE(DATE) MODE,
COUNT(DISTINCT DATE) UNIQUE_VALUES
FROM PUBLIC.LONDON_WEATHER
GROUP BY COL| Q1 | Q3 |
|---|---|
| 5.2 | 9.5 |
This means we can define an outlier that is outside of the following upper and lower bounds:
SELECT
'WEATHER' COL,
MIN(WEATHER) MIN,
MAX(WEATHER) MAX,
MODE(WEATHER) MODE,
COUNT(DISTINCT WEATHER) UNIQUE_VALUES
FROM PUBLIC.LONDON_WEATHER
GROUP BY COL| LOWER_BOUND | UPPER_BOUND |
|---|---|
| -1.25 | 15.95 |
| WINDSPEED |
|---|
| 17.6 |
| 16.2 |
| 16 |
| 16.1 |
| 17.3 |
| 18 |
| 17 |
| 18.4 |
| 18 |
| 16.1 |
| 20.2 |
| 17.8 |
| 19.5 |
| 18.5 |
| 19.5 |
| 22.1 |
| 16.3 |
| 20.3 |
| 16.8 |
| 21.6 |
| 19.2 |
| 17.7 |
| 19.7 |
| 17.7 |
| 16 |
| 18.3 |
| 17.6 |
This page was built using Count. It combines the best features of a SQL IDE, Data Visualization Tool, and Computational Notebooks. In the Count notebook, each cell acts like a CTE, meaning you can reference any other cell in your queries.
This makes not only for far more readable reports (like this one) but also a much faster and more powerful way to do your analysis, essentially turning your analysis into a connected graph of data frames rather than one-off convoluted queries and CSV files. And with a built-in visualization framework, you won't have to export your data to make your charts. Go from raw data to interactive report in one document.
SELECT * FROM
(SELECT 'TEMP' COL_1, 'PRECIPITATION' COL_2, CORR(TEMP,PRECIPITATION) CORR
FROM PUBLIC.LONDON_WEATHER) UNION ALL
(SELECT 'TEMP' COL_1, 'VISIBILITY' COL_2, CORR(TEMP,VISIBILITY) CORR
FROM PUBLIC.LONDON_WEATHER) UNION ALL
(SELECT 'TEMP' COL_1, 'WINDSPEED' COL_2, CORR(TEMP,WINDSPEED) CORR
FROM PUBLIC.LONDON_WEATHER) UNION ALL
(SELECT 'PRECIPITATION' COL_1, 'VISIBILITY' COL_2, CORR(PRECIPITATION,VISIBILITY) CORR
FROM PUBLIC.LONDON_WEATHER) UNION ALL
(SELECT 'PRECIPITATION' COL_1, 'WINDSPEED' COL_2, CORR(PRECIPITATION,WINDSPEED) CORR
FROM PUBLIC.LONDON_WEATHER)
UNION ALL
(SELECT 'VISIBILITY' COL_1, 'WINDSPEED' COL_2, CORR(VISIBILITY,WINDSPEED) CORR
FROM PUBLIC.LONDON_WEATHER)
UNION ALL
(SELECT 'PRECIPITATION' COL_1, 'TEMP' COL_2, CORR(PRECIPITATION,TEMP) CORR
FROM PUBLIC.LONDON_WEATHER)
UNION ALL
(SELECT 'VISIBILITY' COL_1, 'PRECIPITATION' COL_2, CORR(VISIBILITY,PRECIPITATION) CORR
FROM PUBLIC.LONDON_WEATHER)
UNION ALL
(SELECT 'WINDSPEED' COL_1, 'TEMP' COL_2, CORR(WINDSPEED,TEMP) CORR
FROM PUBLIC.LONDON_WEATHER) UNION ALL
(SELECT 'VISIBILITY' COL_1, 'TEMP' COL_2, CORR(VISIBILITY,TEMP) CORR
FROM PUBLIC.LONDON_WEATHER)
UNION ALL
(SELECT 'WINDSPEED' COL_1, 'PRECIPITATION' COL_2, CORR(WINDSPEED,PRECIPITATION) CORR
FROM PUBLIC.LONDON_WEATHER) UNION ALL
(SELECT 'WINDSPEED' COL_1, 'VISIBILITY' COL_2, CORR(WINDSPEED,VISIBILITY) CORR
FROM PUBLIC.LONDON_WEATHER)SELECT PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY WINDSPEED) "Q1",
PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY WINDSPEED) "Q3"
from PUBLIC.LONDON_WEATHERSELECT (Q1-1.5*(Q3-Q1)) LOWER_BOUND , (Q3+1.5*(Q3-Q1)) UPPER_BOUND FROM IQRSelect WINDSPEED from PUBLIC.LONDON_WEATHER cross join IQR where WINDSPEED not between (Q1-1.5*(Q3-Q1)) AND (Q3+1.5*(Q3-Q1))