在 SQL 中分区的正确语法是什么 [英] What is the correct syntax for partitioning in SQL

问题描述

我有一张桌子PatientAppointment.只有 2 列 AppointmentDateTime 和 Duration 用于存储特定患者预约的日期和总小时数或分钟数.让我们假设目前只有一名患者.我面临分区问题.这是表:

I have a table PatientAppointment. There is only 2 columns AppointmentDateTime and Duration which stores date and total hr or minutes for which that particular patient had his appointment. Let us assume there is only one patient for now. I'm facing issues with partitioning. Here is the table:

+---------------------+-----------------+
| AppointmentDateTime | 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        |
+---------------------+-----------------+

这是查询.我首先将持续时间转换为整数，如 30、60、90，以便我可以总结它们.然后我必须按月对它们进行分区.它应该看起来像 db<>fiddle.这适用于小提琴.

Here is the query. I'm first converting duration to minutes in integer like 30, 60, 90 so that I can sum them up. Then I've to partition them month wise. It should look like db<>fiddle. This works on fiddle.

select 
sum(((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))) over (partition by date_format(pa.AppointmentDateTime, '%Y-%m') order by pa.AppointmentDateTime) total
from PatientAppointment pa

这适用于小提琴.但在我的本地出错:

This works on fiddle. But gives error on my local:

错误代码:1064.您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，以在第 3 行的(partition by date_format(pa.AppointmentDateTime, '%Y-%m') order by pa.Appointme'附近使用正确的语法

Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(partition by date_format(pa.AppointmentDateTime, '%Y-%m') order by pa.Appointme' at line 3

我的本​​地版本是:

innodb_version 5.7.26, protocol_version 10, tls_version TLSv1,TLSv1.1,TLSv1.2版本 5.7.26-log

innodb_version 5.7.26, protocol_version 10, tls_version TLSv1,TLSv1.1,TLSv1.2 version 5.7.26-log

请帮我现在做什么.

推荐答案

使用 UDV 的累积和:

Cumulative sum using UDV:

select
dateOfCheckup,
duration,

-- use intermediate variable @cur_dur for not calculate this value twice
@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,

-- check does current @year_month is equal to previous, continue or restart @cum_sum
CASE WHEN @year_month = date_format(dateOfCheckup, '%Y-%m')
     THEN @cum_sum := @cum_sum + @cur_dur
     ELSE @cum_sum := @cur_dur
     END total,

-- store current @year_month for to use with next row
@year_month := date_format(dateOfCheckup, '%Y-%m') monthOfCheckup

from patient, 

-- initialize variables which will be used
(SELECT @year_month:='', @cum_sum:=0, @cur_dur:=0) variables

-- the rows must be processed in definite order
ORDER BY dateOfCheckup

小提琴

输出列的顺序很重要(一行中的输出列中的计算严格按照它们的写入顺序执行).但是这个例子是用作子查询还是按列名访问输出数据都没有关系.

Output columns order is critical (the calulations in output columns in a row are performed strictly in the order in which they're written). But it doesn't matter if this example is used as a subquery or the output data is accessed by column names.

这篇关于在 SQL 中分区的正确语法是什么的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！