查找每行不同日期后平均值的SQL查询
查找每行不同日期后平均值的SQL查询
提问于 2017-05-25 23:05:24
我试图找出在给定日期之前和之后的平均分数，其中每个用户都有自己的日期，我想使用。
我有两个表，第一个表包括座席姓名、分数和日期：
Name Score Date
---- ----- ----
Dan 81 10/1/2016
Brad 35 8/5/2016
Allison 92 6/3/2016
Cindy 95 8/12/2016
Dan 45 7/16/2016
Cindy 77 4/16/2016
Allison 59 3/22/2016
Brad 55 3/22/2016第二个表包括座席姓名和他们接受培训的日期
Agent_name Training_date
---------- ----------
Dan 8/28/2016
Brad 4/15/2016
Cindy 3/3/2016
Allison 5/1/2016我正在寻找的是一个输出，其中包括名称，培训日期，培训前的平均，和培训后的平均。理想情况下看起来像这样
Agent_name Training_date Avg_pre_training Avg_post_training
---------- ------------- ---------------- -----------------
Dan 8/28/2016 45 81
Brad 4/15/2016 55 35
Cindy 3/3/2016 0 86
Allison 5/1/2016 59 92我只是不能得到一个查询，认识到每个人都有自己的日期，我需要考虑。
回答 4
Stack Overflow用户
回答已采纳
发布于 2017-05-25 23:59:38
下面是针对BigQuery标准SQL的说明
#standardSQL
SELECT
Agent_name, Training_date,
ROUND(AVG(CASE WHEN date <= Training_date THEN Score END)) AS Avg_pre_training,
ROUND(AVG(CASE WHEN date > Training_date THEN Score END)) AS Avg_post_training
FROM (
SELECT
Agent_name, Score,
PARSE_DATE('%m/%d/%Y', date) AS date,
PARSE_DATE('%m/%d/%Y', Training_date) AS Training_date
FROM training JOIN agents
ON Name = Agent_name
)
GROUP BY Agent_name, Training_date
-- ORDER BY Agent_name, Training_date您可以使用您的示例中的虚拟数据来执行此查询
#standardSQL
WITH agents AS (
SELECT 'Dan' AS Name, 81 AS Score, '10/1/2016' AS date UNION ALL
SELECT 'Brad', 35, '8/5/2016' UNION ALL
SELECT 'Allison', 92, '6/3/2016' UNION ALL
SELECT 'Cindy', 95, '8/12/2016' UNION ALL
SELECT 'Dan', 45, '7/16/2016' UNION ALL
SELECT 'Cindy', 77, '4/16/2016' UNION ALL
SELECT 'Allison', 59, '3/22/2016' UNION ALL
SELECT 'Brad', 55, '3/22/2016' UNION ALL
SELECT 'Allison', 70, '6/25/2016'
),
training AS (
SELECT 'Dan' AS Agent_name, '8/28/2016' AS Training_date UNION ALL
SELECT 'Brad', '4/15/2016' UNION ALL
SELECT 'Cindy', '3/3/2016' UNION ALL
SELECT 'Allison', '5/1/2016' UNION ALL
SELECT 'Allison', '6/28/2016'
)
SELECT
Agent_name, Training_date,
ROUND(AVG(CASE WHEN date <= Training_date THEN Score END)) AS Avg_pre_training,
ROUND(AVG(CASE WHEN date > Training_date THEN Score END)) AS Avg_post_training
FROM (
SELECT
Agent_name, Score,
PARSE_DATE('%m/%d/%Y', date) AS date,
PARSE_DATE('%m/%d/%Y', Training_date) AS Training_date
FROM training JOIN agents
ON Name = Agent_name
)
GROUP BY Agent_name, Training_date
-- ORDER BY Agent_name, Training_date注意:我添加了一些行，以使示例更通用，以解决同一用户的多个培训的情况
Stack Overflow用户
发布于 2017-05-25 23:37:55
请看我下面的答案，使用我控制的where语句进行前训练和后训练，然后将两个表连接在一起以获得结果集。
CREATE TABLE #SET1
(
NAME VARCHAR(20),
SCORE INT,
[DATE] DATE
)
CREATE TABLE #TRAININGDATE
(
NAME VARCHAR(20),
TRAINING_DATE DATE
)
INSERT INTO #SET1
( NAME, SCORE, DATE )
VALUES
('Dan',81,'10/1/2016'),
('Brad',35,'8/5/2016'),
('Allison',92,'6/3/2016'),
('Cindy',95,'8/12/2016'),
('Dan',45,'7/16/2016'),
('Cindy',77,'4/16/2016'),
('Allison',59,'3/22/2016'),
('Brad',55,'3/22/2016')
INSERT INTO #TRAININGDATE
VALUES
('DAN','8/28/2016'),
('BRAD','4/15/2016'),
('CINDY','3/3/2016'),
('ALLISON','5/1/2016')
SELECT AVG(SCORE) AS AVERAGE_SCORE_BEFORE, A.NAME
INTO #TEMP_A
FROM #SET1 AS A
LEFT JOIN #TRAININGDATE AS B
ON A.NAME = B.NAME
WHERE DATE < B.TRAINING_DATE
GROUP BY A.NAME
SELECT AVG(SCORE) AS AVERAGE_SCORE_AFTER_TRAINING, A.NAME
INTO #TEMP_B
FROM #SET1 AS A
LEFT JOIN #TRAININGDATE AS B
ON A.NAME = B.NAME
WHERE DATE > B.TRAINING_DATE
GROUP BY A.NAME
SELECT A.NAME,ISNULL(B.AVERAGE_SCORE_BEFORE,0) AS AVERAGE_PRE_TRAINING,A.AVERAGE_SCORE_AFTER_TRAINING
FROM #TEMP_B AS A
LEFT JOIN #TEMP_A AS B
ON A.NAME = B.NAMEStack Overflow用户
发布于 2017-05-25 23:44:32
您可以使用派生表来完成此操作：
SELECT T.Agent_Name, T.Training_Date, Avg_Pre_Training, Avg_Post_Training
FROM Training as T
JOIN (SELECT T.Agent_Name, AVG(Score) as Avg_Pre_Training
FROM Training as T
JOIN Scores as S on S.Name= T.Agent_Name
WHERE S.Date < T.Training_Date
GROUP BY T.Agent_Name
) as Pre on Pre.Agent_Name= T.Agent_Name
JOIN (SELECT T.Agent_Name, AVG(Score) as Avg_Post_Training
FROM Training as T
JOIN Scores as S on S.Name= T.Agent_Name
WHERE S.Date >= T.Training_Date
GROUP BY T.Agent_Name
) as Post on Post.Agent_Name= T.Agent_Name 不完全确定我在bigquery中使用别名是正确的，而且这是#legacySQL语法，所以它可能需要一些调整。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/44183800
复制相关文章
相似问题
腾讯云开发者
