用SQL实现ABTest的结果分析-全部Code

treasureisland · 2024-12-11 04:11:47

您需要登录才可以下载或查看附件。没有帐号？注册账号

x

分为四种情况：one sample/two sample, non proportional/proportional
需要深入地理解ttest，同时也要能够在实际的Code中计算出1. One-Sample T-Test (Non-Proportion)

-- Step 1: Define hypothesized mean
WITH hypothesized_mean AS (
SELECT 100.0 AS mu0 -- Replace 100.0 with your hypothesized mean
),
-- Step 2: Calculate sample statistics
sample_stats AS (
SELECT
COUNT(value) AS n,
AVG(value) AS sample_mean,
STDDEV(value) AS sample_stddev -- Use STDDEV_SAMP in some SQL dialects 注意计算sample的standard deviation 有 n与n-1的区别
FROM observations
),
-- Step 3: Calculate the t-statistic
t_statistic AS (
SELECT
(sample_mean - mu0) / (sample_stddev / SQRT(n)) AS t_value, --最重要的公式
n - 1 AS degrees_of_freedom
FROM sample_stats, hypothesized_mean
),
-- Step 4: Return results
results AS (
SELECT
t_value,
degrees_of_freedom,
CASE
WHEN ABS(t_value) >1.96 THEN 'Reject Null Hypothesis' -- Critical value 一般情况下使用z score 为了方便起见
ELSE 'Fail to Reject Null Hypothesis'
END AS conclusion
FROM t_statistic
)
-- Output
SELECT * FROM results;

复制代码

您好！
本帖隐藏的内容需要积分高于 188 才可浏览
您当前积分为 0。
使用VIP即刻解锁阅读权限或查看其他获取积分的方式

portion Test

-- Step 1: Calculate sample statistics for each group
WITH group_stats AS (
SELECT
group,
COUNT(*) AS n,
SUM(outcome) * 1.0 / COUNT(*) AS proportion -- Proportion (p̂)
FROM observations
GROUP BY group
),
-- Step 2: Calculate the standard error squared (SE²)
standard_error_squared AS (
SELECT
g1.group AS group1,
g2.group AS group2,
g1.proportion AS p1,
g2.proportion AS p2,
g1.n AS n1,
g2.n AS n2,
-- Calculate SE² for each group
(g1.proportion * (1 - g1.proportion) / g1.n) +
(g2.proportion * (1 - g2.proportion) / g2.n) AS se_squared
FROM group_stats g1
CROSS JOIN group_stats g2
WHERE g1.group = 'A' AND g2.group = 'B' -- Replace 'A' and 'B' with group identifiers
),
-- Step 3: Calculate the t-statistic
z_statistic AS (
SELECT
(p1 - p2) / SQRT(se_squared) AS t_value
FROM standard_error_squared
),
-- Step 4: Return results
results AS (
SELECT
t_value,
CASE
WHEN ABS(t_value) > 1.96 THEN 'Reject Null Hypothesis'
ELSE 'Fail to Reject Null Hypothesis'
END AS conclusion
FROM t_statistic
)
-- Output
SELECT * FROM results;

复制代码

求点大米～感谢

treasureisland · 2024-12-11 10:00:21

求大家加点米感恩

treasureisland · 2024-12-11 10:00:11

眼神放火的麦片发表于 2024-12-10 14:19
小白路过，想问面试会考这种题吗？哪种岗位面试会考这种题呢？感谢回复。

product ds

treasureisland · 2024-12-11 04:38:36

匿名用户发表于 2024-12-10 12:18
为什么要用sql来写这些呢，Google 的coding 不能用Python写吗？

因为在现实中也存在用sql来写

匿名用户-VOLWR *2024-12-11 04:18:23* · 2024-12-11 04:18:23

为什么要用sql来写这些呢，Google 的coding 不能用Python写吗？

眼神放火的麦片 · 2024-12-11 06:19:23

小白路过，想问面试会考这种题吗？哪种岗位面试会考这种题呢？感谢回复。