Mysql...不可能的查询? [英] Mysql...Impossible query?

问题描述

困难的查询.不确定这是否可能.

Difficult query. Not sure if this is possible.

我想弄清楚如何格式化需要完成几件事的查询.

I am trying to figure out how to format a query that needs to accomplish several things.

(1) 我需要解析 DATE 字段，它是一个 VARCHAR 字段而不是一个 sql 日期字段来获取每个日期的月份.

(1) I need to parse the DATE field that is a VARCHAR field and not a sql date field to get the month of each date.

(2) 然后我需要按 NAME 和 Month 对所有 PTS 字段进行平均.因此，对于下面的示例数据，我将有一行包含 John，John 在 JAN 列中有 2 个，在 APR 列中有 3 个，而 AVG 列将是所有月份的平均值.因此，月份是该月所有条目的平均值，而 AVG 列是该行中所有列的平均值.

(2) I then need to AVG all the PTS fields by NAME and Month. So with my example data below I would have a row that has John and John would have 2 in the JAN column and 3 in the the APR column and the AVG column would be an average of all the months. So the months are an average of all the entries in that month and the AVG column is an average of all the columns in the row.

表格:

Name (VARCAHR)  PTS (INT) DATE (VARCHAR)        
---------------------------------------------
John            3           Tue Apr 14 17:56:02 2020
Chris           2           Tue Apr 14 19:44:03 2020
John            2           Mon Jan 30 15:23:03 2020
Chris           4           Fri Feb 28 16:15:15 2020
John            3           Tue Apr 14 17:56:02 2020


Table Layout on web page:
Name        Jan     Feb     Mar     Apr     May     Jun     Jul     Aug     Sep     Oct     Nov     Dec     Average

推荐答案

并非不可能，只是令人费解.您可以使用 STR_TO_DATE 将您的字符串转换为 DATETIME 对象，然后您可以使用 MONTH 获取月份编号.请注意(正如@DRapp 评论的那样)您应该以其本机形式存储 DATETIME 值，而不是作为 VARCHAR，那么您就不必处理 STR_TO_DATE.获得月份数后，您可以使用条件聚合来获得您想要的结果:

Not impossible, just convoluted. You can use STR_TO_DATE to convert your strings into DATETIME objects, from which you can then use MONTH to get the month number. Note though (as @DRapp commented) that you should be storing DATETIME values in their native form, not as VARCHAR, then you wouldn't have to deal with STR_TO_DATE. Having got the month number, you can then use conditional aggregation to get the results you want:

SELECT name,
       COALESCE(AVG(CASE WHEN mth = 1 THEN PTS END), 0) AS Jan,
       COALESCE(AVG(CASE WHEN mth = 2 THEN PTS END), 0) AS Feb,
       COALESCE(AVG(CASE WHEN mth = 3 THEN PTS END), 0) AS Mar,
       COALESCE(AVG(CASE WHEN mth = 4 THEN PTS END), 0) AS Apr,
       -- repeat for May to November
       COALESCE(AVG(CASE WHEN mth = 12 THEN PTS END), 0) AS `Dec`,
       AVG(PTS) AS AVG
FROM (
  SELECT name, PTS AS PTS, MONTH(STR_TO_DATE(DATE, '%a %b %e %H:%i:%s %Y')) AS mth
  FROM data
) d
GROUP BY name

输出(用于您的示例数据):

Output (for your sample data):

name    Jan     Feb     Mar     Apr     Dec     AVG
Chris   0       4       0       2       0       3
John    0       0       0       2.6667  0       2.6667

SQLFiddle 演示

这篇关于Mysql...不可能的查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！