mysql - 按 id 和月份获取最后一个值、倒数第二个值和倒数第三个值

Question

MySQL 工作台 6.3 。我有以下数据集。

id  date        value
36  12/1/2015   3174
36  11/1/2015   3143
36  10/1/2015   3112
36  9/1/2015    3082
36  8/1/2015    3052
36  7/1/2015    3021
36  6/1/2015    2990
64  11/1/2015   3105
64  10/1/2015   3074
64  8/1/2015    3014
64  7/1/2015    2983
64  6/1/2015    2952
65  12/1/2015   3414
72  10/1/2015   3352
72  9/1/2015    3322
72  8/1/2015    3292
72  7/1/2015    3261
72  6/1/2015    3230
72  5/1/2015    3198
72  4/1/2015    3169
72  3/1/2015    3139
72  2/1/2015    3107
72  1/1/2015    3079

我想要获取的是按 id 分组并获取最近 3 个月的值。(如果原始数据中没有记录，则保留所有日期和值。)下表是我的手动输出，以显示我想要得到的内容。非常感谢。

id  current_month   value1  1_month_before_current  value2  2_month_before_current  value3  3_month_before_current  value3
36  12/1/2015       3174    11/1/2015               3143    10/1/2015               3112    9/1/2015               3082
64  null            null    11/1/2015               3105    10/1/2015               3074    null                   null
72  null            null    null                    null    10/1/2015               3352    9/1/2015               3322

Answer 1

只需使用条件聚合:

select id,
       max(case when date = '2015-12-01' then date end) as current_month,
       max(case when date = '2015-12-01' then value end) as current_value,
       max(case when date = '2015-11-01' then date end) as prev_month,
       max(case when date = '2015-11-01' then value end) as prev_value,
       max(case when date = '2015-10-01' then date end) as prev2_month,
       max(case when date = '2015-10-01' then value end) as prev2_value,
from t
group by id;

如果您不喜欢输入日期:

select id,
       max(case when date = curmon then date end) as current_month,
       max(case when date = curmon then value end) as current_value,
       max(case when date = curmon - interval 1 month then date end) as prev_month,
       max(case when date = curmon - interval 1 month then value end) as prev_value,
       max(case when date = curmon - interval 2 month then date end) as prev2_month,
       max(case when date = curmon - interval 2 month then value end) as prev2_value,
from t cross join
     (select date('2015-12-01') as curmon) params
group by id;

此外，如果日期不全是当月的第一天，您可以在 case 表达式中使用范围逻辑。