如何使用 MySQL 5.7 中的变量实现按功能类型分区 [英] How to achieve partition by kind of functionality using variables in MySQL 5.7

问题描述

我使用的是旧版本的 MySQL.由于一些强烈的原因，我无法升级它.我有一个只有 2 列的表 PatientAppointment.这是表:

+---------------------+-----------------+|日期检查|持续时间 |+---------------------+-----+|2020-05-28 |30 分钟 ||2020-05-29 |30 分钟 ||2020-05-30 |1 小时 ||2020-06-03 |1 小时 ||2020-06-05 |1 小时 30 分钟 ||2020-07-21 |1 小时 30 分钟 ||2020-07-22 |1 小时 ||2020-07-28 |30 分钟 |+---------------------+-----+

我想要 5 样东西:

1. 从 dateOfCheckup
在运行时创建一个新列 monthOfCheckup
2. 将 duration 转换为整数字段作为 minutes
3. 每月(不是日期)累计总计

我通过这个查询达到了这 3 点:

选择体检日期，期间，@cur_dur := ((当持续时间为 '% hour%' 然后 substring_index(duration, 'hour', 1) * 60 else 0 end) +(当持续时间像 '%min%' 然后 substring_index(substring_index(duration, ' min', 1), ' ', -1) + 0 else 0 end)) 作为分钟，CASE WHEN @year_month = date_format(dateOfCheckup, '%Y-%m')然后@cum_sum := @cum_sum + @cur_dur否则@cum_sum := @cur_dur结束总，@year_month := date_format(dateOfCheckup, '%Y-%m') monthOfCheckup来自患者，(SELECT @year_month:='', @cum_sum:=0, @cur_dur:=0) 变量ORDER BY dateOfCheckup

这是相同的 fiddle.

现在剩下的2点是:

4. BillElig1 这可以是布尔值或文本，无关紧要.如果那个特定月份的 total 是 >=20
，我只需要输入Y"
5. BillElig2 这可以是布尔值或文本，无关紧要.如果那个特定月份的 total 是 >=40
，我只需要输入Y"

对于这 2 点，您可以查看此 fiddle.>

请帮帮我.

解决方案

再次使用 UDV.

SELECT dateOfCheckup,期间，分钟，当@prev_month != monthOfCheckup 时的情况那么总 >= 20当@prev_total <20那么 1其他 0END 99457Elig，当@prev_month != monthOfCheckup 时的情况那么总计 >= 40当@prev_total <40那么 1其他 0END 99458Elig，@prev_month := monthOfCheckup monthOfCheckup,@prev_total := 总计从(选择日期检查，期间，@cur_dur := ((当持续时间为 '% hour%' 然后 substring_index(duration, 'hour', 1) * 60 else 0 end) +(当持续时间像 '%min%' 然后 substring_index(substring_index(duration, ' min', 1), ' ', -1) + 0 else 0 end)) 作为分钟，CASE WHEN @year_month = date_format(dateOfCheckup, '%Y-%m')然后@cum_sum := @cum_sum + @cur_dur否则@cum_sum := @cur_dur结束总，@year_month := date_format(dateOfCheckup, '%Y-%m') monthOfCheckup来自患者，(SELECT @year_month:='', @cum_sum:=0, @cur_dur:=0) 变量ORDER BY dateOfCheckup) 子查询，(SELECT @prev_month:=0, @prev_total:=0) 变量ORDER BY dateOfCheckup

小提琴

我使用 1 和 0 而不是 Y 和 NULL.

当然，输出列表中的列顺序和排序也很关键.

I'm using an old version of MySQL. I cannot upgrade it due to some strong reasons. I've a table PatientAppointment with only 2 columns. Here is the table:

+---------------------+-----------------+
| dateOfCheckup       | duration        |
+---------------------+-----------------+
| 2020-05-28          |   30 min        |
| 2020-05-29          |   30 min        |
| 2020-05-30          |   1 hour        |
| 2020-06-03          |   1 hour        |
| 2020-06-05          |   1 hour 30 min |
| 2020-07-21          |   1 hour 30 min |
| 2020-07-22          |   1 hour        |
| 2020-07-28          |   30 min        |
+---------------------+-----------------+

I want 5 things:

1. Create a new column in runtime monthOfCheckup from dateOfCheckup
2. Convert duration into integer field as minutes
3. Accumulating total for each month (not date)

I was able to achieve these 3 points with this query:

select
dateOfCheckup,
duration,
@cur_dur := ((case when duration like '% hour%' then substring_index(duration, ' hour', 1) * 60 else 0 end) +
(case when duration like '%min%' then substring_index(substring_index(duration, ' min', 1), ' ', -1) + 0 else 0 end)) as minutes,

CASE WHEN @year_month = date_format(dateOfCheckup, '%Y-%m')
     THEN @cum_sum := @cum_sum + @cur_dur
     ELSE @cum_sum := @cur_dur
     END total,
@year_month := date_format(dateOfCheckup, '%Y-%m') monthOfCheckup

from patient, (SELECT @year_month:='', @cum_sum:=0, @cur_dur:=0) variables
ORDER BY dateOfCheckup

Here is the fiddle for the same.

Now the remaining 2 points are:

4. BillElig1 This can be boolean or text, doesn't matters. I just have to put 'Y' if the total for that particular month is >=20
5. BillElig2 This can be boolean or text, doesn't matters. I just have to put 'Y' if the total for that particular month is >=40

For these 2 points you can see this fiddle.

Please help me.

解决方案

And again use UDV.

SELECT dateOfCheckup,
       duration,
       minutes,
       CASE WHEN @prev_month != monthOfCheckup
            THEN total >= 20
            WHEN @prev_total < 20 
            THEN 1
            ELSE 0 
            END 99457Elig,
       CASE WHEN @prev_month != monthOfCheckup
            THEN total >= 40
            WHEN @prev_total < 40
            THEN 1
            ELSE 0 
            END 99458Elig,
       @prev_month := monthOfCheckup monthOfCheckup,
       @prev_total := total total
FROM (select dateOfCheckup,
             duration,
             @cur_dur := ((case when duration like '% hour%' then substring_index(duration, ' hour', 1) * 60 else 0 end) +
                         (case when duration like '%min%' then substring_index(substring_index(duration, ' min', 1), ' ', -1) + 0 else 0 end)) as minutes,

             CASE WHEN @year_month = date_format(dateOfCheckup, '%Y-%m')
                  THEN @cum_sum := @cum_sum + @cur_dur
                  ELSE @cum_sum := @cur_dur
                  END total,
             @year_month := date_format(dateOfCheckup, '%Y-%m') monthOfCheckup

      from patient, (SELECT @year_month:='', @cum_sum:=0, @cur_dur:=0) variables
      ORDER BY dateOfCheckup) subquery, 
(SELECT @prev_month:=0, @prev_total:=0) variable
ORDER BY dateOfCheckup

fiddle

I use 1 and 0 instead of Y and NULL.

Of course, the columns order in output list and ordering are critical again.

这篇关于如何使用 MySQL 5.7 中的变量实现按功能类型分区的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！