Task01c：隨機(jī)抽樣與卡方檢驗(yàn)的SQL實(shí)現(xiàn)

需要的工具及基礎(chǔ)學(xué)習(xí)內(nèi)容

- 工具： MySQL【先過一遍書，代碼準(zhǔn)確性具體還得二期編輯，希望用MySQL】

• 書籍：《數(shù)據(jù)分析技術(shù) 使用SQL和EXCEL工具 第二版》
• 數(shù)據(jù)集： 數(shù)據(jù)配套資源
• Task01a：復(fù)習(xí)SQL的基本知識
• Task01b：統(tǒng)計(jì)的基本概念及SQL實(shí)現(xiàn)

主要內(nèi)容

• 抽樣
• 假設(shè)檢驗(yàn)

基于均值的比較

Z分?jǐn)?shù)：測量樣本值到期望值之間的距離，以標(biāo)準(zhǔn)差的數(shù)量測量。

隨機(jī)抽樣及分層抽樣

隨機(jī)樣本是隨機(jī)的，因此，該樣本的統(tǒng)計(jì)是分布于平均值周圍的。

-- 隨機(jī)抽樣 SELECT t.* FROM <tab> t WHERE RAND() < 0.1;-- 可重復(fù)的隨機(jī)抽樣 SELECT t.* FROM <tab> t WHERE RAND(1) < 0.1; -- 偽隨機(jī)數(shù)生成器 WITH t AS (SELECT t.*,ROW_NUMBER() OVER (ORDER BY col) AS seqnumFROM <tab> t ) SELECT t.* FROM t WHERE (seqnum*17+57) % 101 <= 10;-- 分層平衡抽樣 訂單小于$200的200個訂單的散點(diǎn)圖。 SELECT OrderDate,(CASE WHEN PaymentType="AE" THEN TotalPrice END) AS AE,(CASE WHEN PaymentType="AE" THEN NULL ELSE TotalPrice END) AS NotAE FROM Orders WHERE TotalPrice <= 200 ORDER BY RAND() LIMIT 200;

虛擬假設(shè)及可信度

-- 有多少客戶仍然是活躍客戶以及停止率 SELECT COUNT(*) AS numstarts,SUM(CASE WHEN Stoptype IS NOT NULL THEN 1 ELSE 0 END) AS numstops,AVG(CASE WHEN Stoptype IS NOT NULL THEN 1.0 ELSE 0 END) AS stoprate, FROM Subscribers WHERE Startdate = "2005-12-28"-- 1. 給定數(shù)量，停止概率是多少？ -- 2. 給定概率，停止者的數(shù)量是多少？

概率和標(biāo)準(zhǔn)差、置信區(qū)間

-- 標(biāo)準(zhǔn)差以及置信區(qū)間為95%的下限和上限 SELECT stoprate-1.96*stderr AS conflower,stoprate+1.96*stderr AS confupper,stoprate,stderr,numstarts,numstops FROM (SELECT SQRT(stoprate*(1-stoprate)/numstarts) AS stderr,stoprate,numstarts,numstopsFROM (SELECT COUNT(*) AS numstarts,SUM(CASE WHEN Stoptype IS NOT NULL THEN 1 ELSE 0 END) AS numstops,AVG(CASE WHEN Stoptype IS NOT NULL THEN 1.0 ELSE 0 END) AS stoprateFROM SubscribersWHERE startdate = "2005-12-28") s) s

卡方檢驗(yàn) 比較兩者之間的多個維度的區(qū)別。嚴(yán)格地講，偏差是由于偶然導(dǎo)致的可能性是多少？

如果可能性很低，我們就能很自信地認(rèn)為市場之間是有區(qū)別的。

卡方檢驗(yàn)最基本的思想就是通過觀察實(shí)際值與理論值的偏差來確定理論的正確與否。
實(shí)際工作中我們的理論原假設(shè)是：假設(shè)某特征（如性別、年齡）分布與目標(biāo)值（是否流失）的分布相互獨(dú)立，用通俗的話來解釋就是“用戶是不是流失與他的性別無關(guān)”。
當(dāng)我們手頭有一堆樣本數(shù)據(jù)后，可以通過觀察數(shù)據(jù)通過卡方值判斷“原假設(shè)”是否成立。如果不成立，就要推翻原假設(shè)，證明實(shí)際情況是“用戶流失跟性別可能有關(guān)系”。卡方值查P值，若P<0.05則拒絕原假設(shè)，支持性別和是否流失可能相互不獨(dú)立；否則支持原假設(shè)，性別和是否流失相對獨(dú)立，不存在關(guān)聯(lián)。

• 卡方：使用偏移值的平方除以期望值，整個表的卡方值是所有卡方值的和。卡方分布：表的自由度：(行數(shù)-1 )*(列數(shù)-1)

-- SQL中的卡方檢驗(yàn) 不同市場之間是否有區(qū)別 SELECT market,isstopped,val,x,SQUARE(val-x)/x AS chisquare FROM(SELECT cells.market,cells.isstopped,(1.0*r.cnt*c.cnt/(SELECT COUNT(*) FROM subscribers WHERE startdate IN ("2005-12-26"))) AS x,cells.cnt AS valFROM(SELECT Market,(CASE WHEN Stoptype IS NOT NULL THEN 1 ELSE 0 END) AS isstopped,COUNT(*) AS cntFROM SubscribersWHERE Startdate IN ("2005-12-26")GROUP BY Market,(CASE WHEN Stoptype IS NOT NULL THEN 1 ELSE 0 END)) cells LEFT JOIN(SELECT Market,COUNT(*) AS cntFROM SubscribersWHERE startdate IN ("2005-12-26")GROUP BY Market) rON cells.market = r.market LEFT JOIN (SELECT (CASE WHEN stoptype IS NOT NULL THEN 1 ELSE 0 END) AS isstopped,COUNT(*) AS cntFROM SubscribersWHERE startdate IN ("2005-12-26")GROUP BY (CASE WHEN stoptype IS NOT NULL THEN 1 ELSE 0 END)) cON cells.isstopped = c.isstopped) a ORDER BY market,isstopped-- 對產(chǎn)品的偏好與地域相關(guān)嗎？ 產(chǎn)品組和州的組合關(guān)系 SELECT state,GroupName,val,exp,SQUARE(val-expx) / expx AS chisquare FROM (SELECT cells.state,cells.GroupName,1.0*r.cnt*c.cnt / (SELECT COUNT(DISTINCT CustomerId) FROM Orders) AS expx,cells.cnt AS valFROM(SELECT o.State,p.GroupName,COUNT(DISTINCT o.CustomerId) AS cntFROM Orders o LEFT JOIN OrderLines olON o.OrderId = ol.OrderId LEFT JOIN Products pON ol.ProductId = p.ProductIdGROUP BY o.state,p.GroupName) cells LEFT JOIN(SELECT o.state,COUNT(DISTINCT o.CustomerID) AS cntFROM Orders oGROUP BY o.state) rON cells.State = r.State LEFT JOIN(SELECT p.GroupName,COUNT(DISTINCT o.CustomerId) AS cntFROM Orders o LEFT JOIN OrderLines olON o.OrderId = ol.OrderId LEFT JOIN Products pON ol.ProductId = p.ProductIdGROUP BY p.GroupName) cON cells.GroupName = c.GroupName) a ORDER BY chisquare DESC;

多維卡方 月份和支付類型與不同產(chǎn)品類型的特殊關(guān)系

WITH pmg AS (-- 從支付類型、月份、組名聚合計(jì)算單元格值SELECT o.PaymentType,MONTH(o.OrderDate) AS mon,p.GroupName,COUNT(*) AS cntFROM Orders o JOIN OrderLines olON o.OrderId = ol.OrderId JOIN Products pON ol.ProductId = p.ProductIdGROUP BY o.PaymentType,Month(o.OrderDate),p.GroupName),pmgmarg AS (-- 計(jì)算每一個維度的總和SELECT pmg.*,SUM(cnt) OVER (PARTITION BY paymentType) AS cnt_pt,SUM(cnt) OVER (PARTITION BY mon) AS cnt_mon,SUM(cnt) OVER (PARTITION BY GroupName) SA cnt_gn,SUM(cnt) OVER () AS cnt_allFROM pmg),pmgexp AS (-- 計(jì)算期望值SELECT pmgmarg.*,(cnt_pt*cnt_mon*cnt_gn)/POWER(cnt_all,2) AS ExpectedValueFROM pmgmarg) -- 計(jì)算卡方值 SELECT pmgexp.*,SQUARE(cnt-ExpectedValue)/ExpectedValue AS chi2 FROM pmgexp ORDER BY chi2 DESC;

總結(jié)

以上是生活随笔為你收集整理的Task01c：随机抽样与卡方检验的SQL实现的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。