Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitf732ef1

Browse files
authored
Merge pull request#76 from coder2hacker/spandey1296-patch-3
sql query for data science
2 parents8382032 +514f987 commitf732ef1

23 files changed

+781
-0
lines changed

‎Exercise Files/01_01.sql‎

Lines changed: 49 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,49 @@
1+
use AdventureWorksDW2014;
2+
3+
-- select all columns
4+
select*from FactInternetSales;
5+
6+
-- limiting results
7+
selecttop1000*from FactInternetSales;-- in other DB's use limit 1000;
8+
9+
-- select using column names
10+
SELECT [ProductKey]
11+
,[OrderDateKey]
12+
,[DueDateKey]
13+
,[ShipDateKey]
14+
,[CustomerKey]
15+
,[PromotionKey]
16+
,[CurrencyKey]
17+
,[SalesTerritoryKey]
18+
,[SalesOrderNumber]
19+
,[SalesOrderLineNumber]
20+
,[RevisionNumber]
21+
,[OrderQuantity]
22+
,[UnitPrice]
23+
,[ExtendedAmount]
24+
,[UnitPriceDiscountPct]
25+
,[DiscountAmount]
26+
,[ProductStandardCost]
27+
,[TotalProductCost]
28+
,[SalesAmount]
29+
,[TaxAmt]
30+
,[Freight]
31+
,[CarrierTrackingNumber]
32+
,[CustomerPONumber]
33+
,[OrderDate]
34+
,[DueDate]
35+
,[ShipDate]
36+
FROM [dbo].[FactInternetSales]
37+
38+
39+
-- select some and use aliases
40+
SELECTTOP1000
41+
[SalesOrderNumber]as'OrderNumber'
42+
,[SalesOrderLineNumber]as'LineNumber'
43+
,[OrderQuantity]as'Quantity'
44+
,[UnitPrice]as'Price'
45+
,[DiscountAmount]as'Discount'
46+
,[SalesAmount]as'Sales'
47+
,[TaxAmt]as'Taxes'
48+
,[OrderDate]as'Date'
49+
FROM [dbo].[FactInternetSales]

‎Exercise Files/01_02.sql‎

Lines changed: 22 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,22 @@
1+
use AdventureWorksDW2014;
2+
3+
4+
-- inner join
5+
-- returns results only where the join condition is true
6+
select top1000*
7+
from FactInternetSales s
8+
inner join DimProduct pons.ProductKey=p.ProductKey
9+
10+
-- left join
11+
-- returns all rows from sales, regardless of the join condition
12+
select distinct EnglishProductName
13+
from FactInternetSales s
14+
left join DimProduct pons.ProductKey=p.ProductKey
15+
order by1
16+
17+
-- add filter conditions to join
18+
select*
19+
from FactInternetSales s
20+
inner join DimProduct p
21+
ons.ProductKey=p.ProductKey
22+
andp.StartDate>'2013-01-01'

‎Exercise Files/01_03.sql‎

Lines changed: 39 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,39 @@
1+
use AdventureWorksDW2014;
2+
3+
-- basic filter with WHERE
4+
-- get sales of a specific product only
5+
SELECT*
6+
FROM FactInternetSales s
7+
INNER JOIN DimProduct pONs.ProductKey=p.ProductKey
8+
WHEREp.EnglishProductName='Road-650 Black, 62'
9+
10+
-- non-equi-filters
11+
-- get all orders for 2013
12+
SELECT*
13+
FROM FactInternetSales s
14+
INNER JOIN DimProduct pONs.ProductKey=p.ProductKey
15+
WHEREs.OrderDate>='2013-01-01'
16+
ANDs.OrderDate<='2013-12-31'
17+
18+
-- also can use "between" for dates
19+
SELECT*
20+
FROM FactInternetSales s
21+
INNER JOIN DimProduct pONs.ProductKey=p.ProductKey
22+
WHEREs.OrderDate BETWEEN'2013-01-01'AND'2013-12-31';
23+
24+
-- filter for multiple values using IN
25+
SELECT*
26+
FROM FactInternetSales s
27+
INNER JOIN DimProduct pONs.ProductKey=p.ProductKey
28+
WHEREp.EnglishProductNamein(
29+
'Mountain-400-W Silver, 38',
30+
'Mountain-400-W Silver, 40',
31+
'Mountain-400-W Silver, 42',
32+
'Mountain-400-W Silver, 46')
33+
34+
35+
-- find all current and future matches with LIKE
36+
SELECT*
37+
FROM FactInternetSales s
38+
INNER JOIN DimProduct pONs.ProductKey=p.ProductKey
39+
WHEREp.EnglishProductNameLIKE'Mountain%'--put % where you want wildcard

‎Exercise Files/01_04.sql‎

Lines changed: 80 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,80 @@
1+
use AdventureWorksDW2014;
2+
3+
select OrderDate,sum(SalesAmount)
4+
from FactInternetSales
5+
group by OrderDate
6+
order by OrderDate
7+
8+
-- simple aggregations
9+
-- Use additional aggregations to understand more about product sales such as distribution of sales etc..
10+
SELECT
11+
cat.EnglishProductCategoryName'Category'
12+
,sub.EnglishProductSubcategoryName'SubCategory'
13+
,count(1)'Count'-- How many sales where there?
14+
,sum(s.SalesAmount)'Sales'-- How much sales did we have?
15+
,avg(s.SalesAmount)'Avg_SalesAmount'-- What was the Avg sale amount?
16+
,min(s.SalesAmount)'Min_SaleAmount'-- What was the Min sale amount?
17+
,max(s.SalesAmount)'Max_SaleAmount'-- What was the Max sale amount
18+
FROM FactInternetSales s
19+
LEFT JOIN DimProduct pONs.ProductKey=p.ProductKey
20+
LEFT JOIN DimProductSubcategory subONp.ProductSubcategoryKey=sub.ProductSubcategoryKey
21+
LEFT JOIN DimProductCategory catONsub.ProductCategoryKey=cat.ProductCategoryKey
22+
-- must use group by in order for aggregation to work properly
23+
GROUP BY
24+
cat.EnglishProductCategoryName-- column aliases aren't allowed
25+
,sub.EnglishProductSubcategoryName
26+
ORDER BY
27+
cat.EnglishProductCategoryName
28+
,sub.EnglishProductSubcategoryName
29+
30+
-- filter to 2013 with WHERE
31+
SELECT
32+
YEAR(s.OrderDate)'Year'
33+
,cat.EnglishProductCategoryName'Category'
34+
,sub.EnglishProductSubcategoryName'SubCategory'
35+
,count(1)'Count'-- use 1 instead of a field for faster performance
36+
,sum(s.SalesAmount)'Sales'
37+
,avg(s.SalesAmount)'Avg_Quantity'
38+
,min(s.SalesAmount)'Min_SaleAmount'
39+
,max(s.SalesAmount)'Max_SaleAmount'
40+
41+
FROM FactInternetSales s
42+
INNER JOIN DimProduct pONs.ProductKey=p.ProductKey
43+
INNER JOIN DimProductSubcategory subONp.ProductSubcategoryKey=sub.ProductSubcategoryKey
44+
INNER JOIN DimProductCategory catONsub.ProductCategoryKey=cat.ProductCategoryKey
45+
-- filter
46+
WHERE YEAR(s.OrderDate)=2013--use date function to parse year
47+
-- must use group by in order for aggregation to work properly
48+
GROUP BY
49+
YEAR(s.OrderDate)
50+
,cat.EnglishProductCategoryName-- column aliases aren't allowed
51+
,sub.EnglishProductSubcategoryName
52+
ORDER BY
53+
cat.EnglishProductCategoryName
54+
,sub.EnglishProductSubcategoryName
55+
56+
-- Only show products in 2013 that sold more than $1M USD
57+
SELECT
58+
cat.EnglishProductCategoryName'Category'
59+
,sub.EnglishProductSubcategoryName'SubCategory'
60+
,count(1)'Count'-- use 1 instead of a field for faster performance
61+
,sum(s.SalesAmount)'Sales'
62+
,avg(s.SalesAmount)'Avg_Quantity'
63+
,min(s.SalesAmount)'Min_SaleAmount'
64+
,max(s.SalesAmount)'Max_SaleAmount'
65+
FROM FactInternetSales s
66+
INNER JOIN DimProduct pONs.ProductKey=p.ProductKey
67+
INNER JOIN DimProductSubcategory subONp.ProductSubcategoryKey=sub.ProductSubcategoryKey
68+
INNER JOIN DimProductCategory catONsub.ProductCategoryKey=cat.ProductCategoryKey
69+
-- filter
70+
WHERE YEAR(s.OrderDate)=2013--use date function to parse year
71+
-- must use group by in order for aggregation to work properly
72+
GROUP BY
73+
cat.EnglishProductCategoryName-- column aliases aren't allowed
74+
,sub.EnglishProductSubcategoryName
75+
-- use HAVING to filter after the aggregate is computed
76+
HAVING
77+
sum(s.SalesAmount)>1000000
78+
ORDER BY
79+
cat.EnglishProductCategoryName
80+
,sub.EnglishProductSubcategoryName

‎Exercise Files/01_05.sql‎

1.55 KB
Binary file not shown.

‎Exercise Files/01_06.sql‎

Lines changed: 46 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,46 @@
1+
use AdventureWorksDW2014;
2+
3+
-- Sub Queries
4+
5+
-- Use a sub-query to aggregate an underlying Table
6+
select*
7+
from (
8+
selectsum(SalesAmount)as'Sales', YEAR(OrderDate)as'Yr'
9+
from FactInternetSales
10+
group by YEAR(OrderDate)
11+
) YrSales
12+
13+
-- Create new aggregates on to of derived
14+
selectavg(Sales)as'AvgSales'
15+
from (
16+
selectsum(SalesAmount)as'Sales', YEAR(OrderDate)as'Yr'
17+
from FactInternetSales
18+
group by YEAR(OrderDate)
19+
) YrSales
20+
21+
-- Use a subquery to test if values are IN another table
22+
SELECT EnglishProductName'Product'
23+
FROM DimProduct p
24+
WHEREp.ProductSubcategoryKeyIN
25+
(SELECTsc.ProductSubcategoryKey
26+
FROM DimProductSubcategory sc
27+
WHEREsc.EnglishProductSubcategoryName='Wheels')
28+
29+
-- Re-write this as a Join instead
30+
SELECTp.EnglishProductName
31+
FROMDimProduct p
32+
JOINDimProductSubcategory scONp.ProductSubcategoryKey=sc.ProductSubcategoryKey
33+
WHEREsc.EnglishProductSubcategoryName='Wheels'
34+
35+
-- Use EXISTS to test if the outer queries value is present in the sub-query
36+
-- Somtimes this is the only way to express this join type
37+
SELECT EnglishProductName'Product'
38+
FROM DimProduct p
39+
WHERE EXISTS
40+
(SELECT*-- no data is returned, only a boolean true/false
41+
FROM DimProductSubcategory sc
42+
WHEREp.ProductSubcategoryKey=sc.ProductSubcategoryKey
43+
ANDsc.EnglishProductSubcategoryName='Wheels')
44+
45+
46+

‎Exercise Files/01_07.sql‎

Lines changed: 52 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,52 @@
1+
use AdventureWorksDW2014;
2+
3+
-- Show a 6 week rolling average of Weekly Sales for 2013
4+
5+
-- first create weekly sales totals
6+
SELECTSUM(s.SalesAmount)'WeeklySales'
7+
,DATEPART(ww,s.OrderDate)as'WeekNum'
8+
FROMFactInternetSales s
9+
WHEREYEAR(s.OrderDate)=2013
10+
GROUP BY
11+
DATEPART(ww,s.OrderDate)
12+
ORDER BY
13+
DATEPART(ww,s.OrderDate)ASC
14+
15+
-- use that subquery as our source and calculate the moving average
16+
SELECT
17+
AVG(WeeklySales) OVER (ORDER BY WeekNum ROWS BETWEEN6 PRECEDINGAND CURRENT ROW)as AvgSales
18+
,WeeklySalesas'TotalSales'
19+
,WeekNum
20+
FROM (
21+
SELECTSUM(s.SalesAmount)'WeeklySales'
22+
,DATEPART(ww,s.OrderDate)as'WeekNum'
23+
FROMFactInternetSales s
24+
WHEREYEAR(s.OrderDate)=2013
25+
GROUP BY
26+
DATEPART(ww,s.OrderDate)
27+
)AS s
28+
GROUP BY
29+
WeekNum, WeeklySales
30+
ORDER BY
31+
WeekNumASC
32+
33+
34+
-- Running Total
35+
SELECT
36+
SUM(MonthlySales) OVER (PARTITION BY SalesYearORDER BY SalesMonth ROWS UNBOUNDED PRECEDING)as YTDSales
37+
,MonthlySalesas'MonthlySales'
38+
,SalesYear
39+
,SalesMonth
40+
FROM (
41+
SELECTSUM(s.SalesAmount)'MonthlySales'
42+
,MONTH(s.OrderDate)as'SalesMonth'
43+
,year(s.OrderDate)as'SalesYear'
44+
FROMFactInternetSales s
45+
GROUP BY
46+
MONTH(s.OrderDate)
47+
,year(s.OrderDate)
48+
)AS s
49+
GROUP BY
50+
SalesMonth, SalesYear, MonthlySales
51+
ORDER BY
52+
SalesYear, SalesMonthASC

‎Exercise Files/01_08.sql‎

Lines changed: 60 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,60 @@
1+
use AdventureWorksDW2014;
2+
3+
-- Employee Table
4+
select*
5+
from DimEmployee
6+
7+
-- Analyzing Employee Data
8+
-- How many active employees did we have on Nov 13th, 2013?
9+
SELECTCOUNT(1)
10+
FROM DimEmployee emp
11+
WHERE StartDate<='2013-01-01'
12+
AND(
13+
EndDate>'2013-01-01'
14+
OR
15+
EndDate ISNULL
16+
)
17+
18+
-- start with dates table
19+
select top100*
20+
from DimDate
21+
22+
-- Show me a trend of active employees by Month
23+
-- Start by getting the Daily count
24+
SELECT
25+
dt.FullDateAlternateKeyas'Date'
26+
,count(1)as ActiveCount
27+
FROM DimDate dt
28+
LEFT JOIN(SELECT'Active'as'EmpStatus',*FROM DimEmployee) emp
29+
-- regular active employees
30+
ON (dt.FullDateAlternateKey betweenemp.StartDateand ISNULL(emp.EndDate,'9999-12-31'))
31+
GROUP BY
32+
dt.FullDateAlternateKey
33+
ORDER BY
34+
1
35+
36+
-- Show EOM Function
37+
select DISTINCT top20 EOMONTH(FullDateAlternateKey)
38+
from DimDate d
39+
order by1
40+
41+
42+
-- These counts are cumulative, so for monthly totals take the last day of the month
43+
SELECT
44+
dt.FullDateAlternateKeyas'Date'
45+
,count(1)as ActiveCount
46+
FROM DimDate dt
47+
LEFT JOIN(SELECT'Active'as'EmpStatus',*FROM DimEmployee) emp
48+
-- regular active employees
49+
ON (dt.FullDateAlternateKey betweenemp.StartDateand ISNULL(emp.EndDate,'9999-12-31'))
50+
WHERE
51+
dt.FullDateAlternateKey= EOMONTH(dt.FullDateAlternateKey)
52+
GROUP BY
53+
dt.FullDateAlternateKey
54+
ORDER BY
55+
1
56+
57+
58+
59+
60+

‎Exercise Files/01_09.sql‎

3.03 KB
Binary file not shown.

‎Exercise Files/01_10.sql‎

3.37 KB
Binary file not shown.

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp