CHJBootCamp2018/DAY2

CHJBootCamp2018

SQL(2) 弘伙□皿祭﹜刓及瑛寧

↑

弘伙□皿祭午螂煌

↑

弘伙□皿祭“ GROUP BY 嗥

SELECT 昫抩,昫抩2... FROM 氾□皮伙 GROUP BY 昫抩
- 羶簽匏刓井日圴髦互濤糧髦匹丐月手及毛⑺元賄袓豳,賄袓豳厶心及手及仍午卞引午戶化賄袓豳,賄袓豳厶心及昫毛請恘
```
SELECT 賄袓豳,賄袓豳厶心 FROM 羶簽匏 WHERE 圴髦='濤糧髦' GROUP BY 賄袓豳,賄袓豳厶心
```

↑

ORDER BY 嗥 (汊蓮)

SELECT 昫抩,昫抩2... FROM 氾□皮伙 GROUP BY 昫抩 ORDER BY 昫抩
- 羶簽匏刓井日圴髦互濤糧髦匹丐月手及毛⑺元賄袓豳,賄袓豳厶心及手及仍午卞引午戶化賄袓豳,賄袓豳厶心及昫毛賄袓豳厶心,賄袓豳賜卞請恘
```
SELECT 賄袓豳,賄袓豳厶心 FROM 羶簽匏 WHERE 圴髦='濤糧髦' GROUP BY 賄袓豳,賄袓豳厶心 ORDR BY 賄袓豳厶心,賄袓豳
```

↑

COUNT()“螂煌楮醒

螂煌仄凶醒毛忒允
- 羶簽匏刓井日圴髦互濤糧髦匹丐月手及毛⑺元賄袓豳,賄袓豳厶心及手及仍午卞引午戶化賄袓豳,賄袓豳厶心及昫午螂煌仄凶賄醒毛請恘
```
SELECT 賄袓豳,賄袓豳厶心,count(賄袓豳) FROM 羶簽匏 WHERE 圴髦='濤糧髦' GROUP BY 賄袓豳,賄袓豳厶心
```
- 羶簽匏刓井日圴髦互濤糧髦匹丐月手及毛⑺元賄袓豳,賄袓豳厶心及手及仍午卞引午戶化賄袓豳,賄袓豳厶心及昫午螂煌仄凶賄醒毛賄袓豳厶心﹋失奶它巨左﹌賜匹請恘
```
SELECT 賄袓豳,賄袓豳厶心,count(賄袓豳) FROM 羶簽匏 WHERE 圴髦='濤糧髦' GROUP BY 賄袓豳,賄袓豳厶心 ORDER BY 賄袓豳厶心
```
- 羶簽匏刓井日圴髦互濤糧髦匹丐月手及毛⑺元賄袓豳,賄袓豳厶心及手及仍午卞引午戶化賄袓豳,賄袓豳厶心及昫午螂煌仄凶賄醒毛賄醒及聶中賜匹請恘
```
SELECT 賄袓豳,賄袓豳厶心,count(賄袓豳) FROM 羶簽匏 WHERE 圴髦='濤糧髦' GROUP BY 賄袓豳,賄袓豳厶心 ORDER BY count(賄袓豳) DESC
```

↑

刓及瑛寧

〇朿瑛寧 INNER JOIN
- 瑛寧橢瘀卞域譙允月伊戊□玉分仃毛刓憎
陸朿瑛寧 LEFT (OUTER) JOIN
- 撈卞允月爾及氾□皮伙及伊戊□玉蟈化午﹜瑛寧橢瘀卞域譙允月惘氾□皮伙及伊戊□玉毛刓憎﹝域譙允月手及互卅中樺寧﹜惘礎反NULL﹋襖卅仄﹌午卅月﹝

↑

踏鳴樹扷氾□皮伙午瑛寧

羶簽匏’贏簽匏氾□皮伙午反扔件皿伙ID匹瑛寧允月
- 优內戊□由旦卞覆殺允月踏鳴樹扷互丐月﹋瑛寧匹五卅中仇午反卅中﹌
```
select top 100 *
from 羶簽匏 as S inner join 踏鳴樹扷 as B on S.扔件皿伙ID=B.扔件皿伙ID
```

↑

羶簽匏’贏簽匏氾□皮伙毛瑛寧﹋陸朿瑛寧﹌

羶簽匏午贏簽匏及瑛寧卞反扔件皿伙ID午妐儂釩銨匏譆毛銀丹﹋洘⺀反羶簽匏午贏簽匏匹唱卅月﹌
- 覆殺允月贏簽匏互卅中樺寧﹋贏簽匏及籣醱卞丐月羶簽匏﹌﹜NULL卞卅月﹝
```
select top 100 *
from 羶簽匏 as S left join 贏簽匏 as L on S.扔件皿伙ID=L.扔件皿伙ID and S._妐儂釩銨匏譆=L._妐儂釩銨匏譆
```

↑

殺迕“厭嶽邰豳午仄化踞⑹髦毛殖戈贏簽匏抩髦

select s.扔件皿伙ID,s.洘⺀,s.賄袓豳,l.平□,l.賄袓豳 from 贏簽匏 as l
inner join 羶簽匏 as s
 on s.扔件皿伙ID=l.扔件皿伙ID
  and s._妐儂釩銨匏譆>=l._妐儂釩銨匏譆
  and s._妐儂釩銨匏譆<l._妐儂蔽弇匏譆
where s.扔皮戊□由旦抩='尪兜'
and s.圴髦 like '踞⑹髦%' and l.圴髦 like '抩髦%'

↑

≠怀簫∞

select * from 匹氾□皮伙及蟈化及昫毛手勻化仁月
select count(*) as 抩蟆 from 氾□皮伙 as X﹛及方丹卞昫支氾□皮伙卞抩蟆﹋巨奶伉失旦﹌毛尥仃日木月
- 贏中氾□皮伙抩支﹜螂煌瑛絆卅升及抩拑仄及昫匹銀丹
犯□正矛□旦及NULL反﹜�橘卞冂潰卅襖
- 忔騷卞反⺶勝匹五卅中及匹﹜is null支ISNULL楮醒羈毛銀丹

↑

刓及瑛寧午螂煌

踏鳴樹扷氾□皮伙午瑛寧仄化綜圴帎及賄醒毛螂煌

select 綜圴抩, count(*) as 鍚賄醒
from 羶簽匏 as S inner join 踏鳴樹扷 as B on S.扔件皿伙ID=B.扔件皿伙ID
where S.扔皮戊□由旦抩 like '尪兜'
group by 綜圴抩

↑

SQL(3) 戊□由旦犯□正及健中

↑

楮醒及中欠中欠

↑

賺心慇心楮醒

https://msdn.microsoft.com/ja-jp/library/ms174318(v=sql.120).aspx

↑

妐儂昫楮醒

Excel匹銀勻凶及午⑺元方丹卅手及互銀尹月互抩蟆互啜丹手及手
- Excel及SEARCH ﹥ charindex,patindex
- Excel及MID ﹥ substring﹛卅升

↑

KWIC戲嶽楮醒﹋交□扒□爛聒楮醒﹌

KWIC戲嶽
- fn蟆妐怌(扔件皿伙ID,請蜇踏儂溥釩銨匏譆,贏今)
- fn詨妐怌(扔件皿伙ID,請蜇踏儂溥釩銨匏譆,贏今)

select top 100 dbo.fn蟆妐怌(扔件皿伙ID,請蜇踏儂溥釩銨匏譆,20),平□,dbo.fn詨妐怌(扔件皿伙ID,請蜇踏儂溥釩銨匏譆,20) from 羶簽匏

select top 100 扔件皿伙ID,dbo.fn蟆妐怌(扔件皿伙ID,請蜇踏儂溥釩銨匏譆,20),平□,dbo.fn詨妐怌(扔件皿伙ID,請蜇踏儂溥釩銨匏譆,20),賄袓豳,賄袓豳厶心,圴髦 from 羶簽匏
where 賄袓豳 like '捧'

↑

BCCWJ犯□正毛銀丹卞反

use chunagon_bccwj

CHJ午幻午氏升⑺元犯□正厭瞻﹝扔奶朮互釐五中仇午卞鏜啦
扔皮戊□由旦抩匹卅仁BCCWJ反＞伊斥旦正□＝*1

↑

CASE摯毛勾井勻凶螂煌

CASE WHEN ∥ THEN ↘↘ WHEN ╯ THEN ╰╰ ELSE ═ END

 select 綜圴抩, count(*) as 鍚賄醒,sum(CASE WHEN 呿妐潘帎='統' THEN 1 ELSE 0 END) as 統賄醒
 from 羶簽匏 as S inner join 踏鳴樹扷 as B on S.扔件皿伙ID=B.扔件皿伙ID
 where S.扔皮戊□由旦抩 like '尪兜'
 group by 綜圴抩

◢呿妐潘帎匹弘伙□皿祭仄化請恘仄凶刓毛Excel及疋示永玄氾□皮伙匹螂煌仄凶杅互鍍仁化棉啜尹卞仁中樺寧手﹝

↑

洘糧允月賄及潸曰請仄

⑺域及刓毛域賄內勾內日仄卅互日瑛寧允月﹝戊□由旦犯□正及健中匹奷請允月氾弁瓦永弁﹝

﹋蟆杅詨杅隋粟卞方月腹綢橢瘀隙爛互匹五月﹌

select top 100 s1.賄袓豳,s1.賄袓豳厶心,s1.圴髦,dbo.fn蟆妐怌(s1.扔件皿伙ID,s1.請蜇踏儂溥釩銨匏譆,20),s1.平□,dbo.fn詨妐怌(s1.扔件皿伙ID,s1.請蜇踏儂溥釩銨匏譆,20)
from 羶簽匏 as s1 inner join 羶簽匏 as s2 on s1.扔件皿伙ID=s2.扔件皿伙ID and s1.洘⺀ + 10 = s2.洘⺀
where s2.賄袓豳 like '捧'

≠馨怀∞＞艘＝及域勾蟆卞侳月溥芢髦洘蟲溥及迕拺

select s1.扔件皿伙ID,s1.請蜇踏儂溥釩銨匏譆,
dbo.fn蟆妐怌(s1.扔件皿伙ID,s1.請蜇踏儂溥釩銨匏譆,20),s1.平□,dbo.fn詨妐怌(s1.扔件皿伙ID,s1.請蜇踏儂溥釩銨匏譆,20),s1.賄袓豳,s1.賄袓豳厶心,s1.葆妐妐儂昫
from 羶簽匏 as s1
inner join 羶簽匏 as s2 on s1.扔件皿伙ID=s2.扔件皿伙ID and s1.洘⺀+10 = s2.洘⺀
where s1.圴髦 like '溥芢髦%' and s1.唾迕溥 like '洘蟲溥%' and s2.賄袓豳 = '艘'

↑

N-gram

賄袓豳6弘仿丞

select top 100
s1.平□+s2.平□+s3.平□+s4.平□+s5.平□+s6.平□,
s1.賄袓豳+'/'+s2.賄袓豳+'/'+s3.賄袓豳+'/'+s4.賄袓豳+'/'+s5.賄袓豳+'/'+s6.賄袓豳
from 羶簽匏 as s1
 inner join 羶簽匏 as s2 on s1.扔件皿伙ID=s2.扔件皿伙ID and s1.洘⺀ + 10 = s2.洘⺀
 inner join 羶簽匏 as s3 on s2.扔件皿伙ID=s3.扔件皿伙ID and s2.洘⺀ + 10 = s3.洘⺀
 inner join 羶簽匏 as s4 on s3.扔件皿伙ID=s4.扔件皿伙ID and s3.洘⺀ + 10 = s4.洘⺀
 inner join 羶簽匏 as s5 on s4.扔件皿伙ID=s5.扔件皿伙ID and s4.洘⺀ + 10 = s5.洘⺀
 inner join 羶簽匏 as s6 on s5.扔件皿伙ID=s6.扔件皿伙ID and s5.洘⺀ + 10 = s6.洘⺀
where s1.扔皮戊□由旦抩='尪兜' 
order by s1.扔件皿伙ID, s1.洘⺀

≠馨怀∞賄袓豳6弘仿丞及螂煌(聶中手及賜﹜筏寞羈毛殖引卅中)

select top 100
s1.賄袓豳+'/'+s2.賄袓豳+'/'+s3.賄袓豳+'/'+s4.賄袓豳+'/'+s5.賄袓豳+'/'+s6.賄袓豳 , count(*)
from 羶簽匏 as s1
 inner join 羶簽匏 as s2 on s1.扔件皿伙ID=s2.扔件皿伙ID and s1.洘⺀ + 10 = s2.洘⺀
 inner join 羶簽匏 as s3 on s2.扔件皿伙ID=s3.扔件皿伙ID and s2.洘⺀ + 10 = s3.洘⺀
 inner join 羶簽匏 as s4 on s3.扔件皿伙ID=s4.扔件皿伙ID and s3.洘⺀ + 10 = s4.洘⺀
 inner join 羶簽匏 as s5 on s4.扔件皿伙ID=s5.扔件皿伙ID and s4.洘⺀ + 10 = s5.洘⺀
 inner join 羶簽匏 as s6 on s5.扔件皿伙ID=s6.扔件皿伙ID and s5.洘⺀ + 10 = s6.洘⺀
where s1.扔皮戊□由旦抩='抸慫’釐懇' 
and s1.圴髦+s2.圴髦+s3.圴髦+s4.圴髦+s5.圴髦+s6.圴髦 not like '%筏寞%'
and s1.圴髦+s2.圴髦+s3.圴髦+s4.圴髦+s5.圴髦+s6.圴髦 not like '%塢ヶ%'
group by s1.賄袓豳+'/'+s2.賄袓豳+'/'+s3.賄袓豳+'/'+s4.賄袓豳+'/'+s5.賄袓豳+'/'+s6.賄袓豳
order by count(*) desc

↑

≠馨怀∞N賄動〇及隙爛

＞罹丹＝及蟆杅5賄動〇卞侳月抩髦≒毛

select s1.扔件皿伙ID,s1.請蜇踏儂溥釩銨匏譆,
dbo.fn蟆妐怌(s1.扔件皿伙ID,s1.請蜇踏儂溥釩銨匏譆,20),s1.平□,dbo.fn詨妐怌(s1.扔件皿伙ID,s1.請蜇踏儂溥釩銨匏譆,20)
from 羶簽匏 as s1
 inner join 羶簽匏 as s2 on s1.扔件皿伙ID=s2.扔件皿伙ID and s1.洘⺀+10 = s2.洘⺀
 inner join 羶簽匏 as s3 on s2.扔件皿伙ID=s3.扔件皿伙ID and s2.洘⺀+50 >= s3.洘⺀  and s2.洘⺀ < s3.洘⺀
where s1.圴髦 like '抩髦%' and s2.賄袓豳 = '毛' and s3.賄袓豳 ='罹丹'

↑

妐醒’妐濡醒’賄醒

select 綜圴抩--,S.呿妐潘帎
,sum(case when S.妐雁釵 = 'B' then 1 else 0 end) as 妐醒
,sum(case when 妐濡='B' then 1 else 0 end) as 妐濡醒
,count(L.賄袓豳) as 贏簽匏醒
,count(S.賄袓豳) as 羶簽匏醒
from 羶簽匏 as S inner join 踏鳴樹扷 as B on S.扔件皿伙ID = B.扔件皿伙ID
left join 贏簽匏 as L on S.扔件皿伙ID = L.扔件皿伙ID and S._妐儂釩銨匏譆 = L._妐儂釩銨匏譆
where B.扔皮戊□由旦抩 like '尪兜'
group by B.綜圴抩--,S.呿妐潘帎

↑

扔皮弁巨伉

喟裡匹仁仁勻凶SELECT妐卞抩蟆毛尥仃化﹜丐凶井手氾□皮伙匹丐月井及方丹卞健尹月

唱卅曰賄醒

select x.扔皮戊□由旦抩, count(*) from
 (select 扔皮戊□由旦抩, count(*) as 賄醒 from 羶簽匏 where 圴髦 not like '筏寞%'
  group by 賄袓豳ID,扔皮戊□由旦抩) as x
group by x.扔皮戊□由旦抩

TTR﹋Type Token Ratio﹌

select x.扔皮戊□由旦抩, count(*) as 唱卅曰賄醒, sum(賄醒) as 曹屯賄醒, 
CONVERT(float,count(*))/sum(賄醒) as TTR from
 (select 扔皮戊□由旦抩, count(*) as 賄醒 from 羶簽匏 where 圴髦 not like '%筏寞%'
  group by 賄袓豳ID,扔皮戊□由旦抩) as x
group by x.扔皮戊□由旦抩

↑

憮踏犯□正矛□旦﹋unidicSQL﹌

↑

憮踏及閉遽祭今木凶葦請仄刓毛健丹

use unidicSQL

賄袓豳
- 賄溥
  - 踏儂溥

賄袓豳毛請恘

select * from 羶簽匏賄袓豳 where 賄袓豳 like '笙%' and 賄袓豳厶心 like '瓜汁%'

賄袓豳及蟈化及賄溥,じ恘唾迕滇毛請恘

SELECT 賄溥,じ恘唾迕滇 from 羶簽匏賄溥 as F inner join 羶簽匏賄袓豳 as L on L.賄袓豳ID=F.賄袓豳ID 
where 賄袓豳 like '葦月' and 賄袓豳厶心 like '立伙'

賄袓豳及蟈化及踏儂溥毛請恘

SELECT 賄溥,踏儂溥 from 羶簽匏踏儂溥 as O inner join 羶簽匏賄溥 as F on F.賄溥ID=O.賄溥ID inner join 羶簽匏賄袓豳 as L on L.賄袓豳ID=F.賄袓豳ID 
where 賄袓豳 like '葦月' and 賄袓豳厶心 like '立伙'

↑

戊□由旦午憮踏毛煉什

賄袓豳ID, 賄溥ID, 踏儂溥ID

use chunagon_bccwj
select L.賄袓豳,SUW.伊斥旦正□,count(SUW.平□) from 羶簽匏賄袓豳 as L inner join chunagon_bccwj.dbo.羶簽匏 as SUW on L.賄袓豳ID=SUW.賄袓豳ID
where L.賄袓豳 like '笙%' and L.賄袓豳厶心 like '瓜汁%'
group by L.賄袓豳, SUW.伊斥旦正□

曉午⑺元手及毛陸朿瑛寧

use chunagon_bccwj
select L.賄袓豳,SUW.伊斥旦正□,count(SUW.平□) from 羶簽匏賄袓豳 as L left join chunagon_bccwj.dbo.羶簽匏 as SUW on L.賄袓豳ID=SUW.賄袓豳ID
where L.賄袓豳 like '笙%' and L.賄袓豳厶心 like '瓜汁%'
group by L.賄袓豳, SUW.伊斥旦正□

↑

域凜氾□皮伙及綜嶽

#匹銨引月氾□皮伙抩反﹜DB濠蠅詨綽輪今木月域凜氾□皮伙午卅月﹝蜇箕及腺蜃匹手域凜氾□皮伙反綜嶽匹五月﹝
呁豪卅扔皮弁巨伉毛踏仁方曰﹜域凜氾□皮伙卞踏五請仄凶杅互質咥互鍍中仇午互聶中

SELECT 市仿丞 INTO 蕙仄中踏五慇心燮及氾□皮伙 FROM 氾□皮伙 WHERE橢瘀嗥

腺蜃互卅中及匹巨仿□

select * into 捧氾□皮伙 from 羶簽匏 where 賄袓豳='捧'

select * into #捧氾□皮伙 from 羶簽匏 where 賄袓豳='捧'

select * from #捧氾□皮伙

drop table“氾□皮伙及綽輪﹋域凜氾□皮伙動陸反腺蜃曉綽輪匹五卅中﹌
```
drop table #捧氾□皮伙
```

↑

≠馨怀∞殺迕拺“筏寞毛殖引卅中羶簽匏氾□皮伙毛綜月

select ROW_NUMBER() OVER (PARTITION BY 扔件皿伙ID ORDER BY 扔件皿伙ID,洘⺀) *10 as 蕙洘⺀, *
into #筏寞卅仄羶簽匏
from 羶簽匏 where 扔皮戊□由旦抩='尪兜'
and 圴髦 not like '%筏寞%' and 圴髦 != '塢ヶ'

select * from #筏寞卅仄羶簽匏 ORDER BY 扔件皿伙ID,蕙洘⺀

↑

≠馨怀2018/08/31∞妐⺀寞及厙迕

妐⺀寞及昫 _妐⺀寞互餵尹引仄凶﹝
仇木毛銀丹仇午匹⑺域妐醱及隋粟賄卅升毛棵簽卞葦勾仃月仇午互匹五引允﹝

select s1.扔件皿伙ID,s1.請蜇踏儂溥釩銨匏譆,
dbo.fn蟆妐怌(s1.扔件皿伙ID,s1.請蜇踏儂溥釩銨匏譆,30),s1.平□,dbo.fn詨妐怌(s1.扔件皿伙ID,s1.請蜇踏儂溥釩銨匏譆,30),s1.賄袓豳,s1.賄袓豳厶心,s1.葆妐妐儂昫
from 羶簽匏 as s1
inner join 羶簽匏 as s2 on s1.扔件皿伙ID=s2.扔件皿伙ID and s1._妐⺀寞 = s2._妐⺀寞
where s1.賄袓豳 like '蔗'  and s2.賄袓豳 like '芞%' 
order by s1.扔件皿伙ID,s1.請蜇踏儂溥釩銨匏譆

↑