MySQL中的Postgres窗口函数lag（）等效查询 [英] Postgres window function lag() equivalent query in MySQL

问题描述

我有以下postgresql脚本：

I have postgresql's script as follow as:

select student,
       data, 
       number,
       number - lag(number,1,number) over (partition by student order by id) as output
from the_table
order by student, id

但是我不使用postgresql，而是使用mysql，当我在mysql中尝试该脚本时，该脚本是错误的，因此如何转换mysql中的脚本？

but I'm not use postgresql, I use mysql, when I will try that script in mysql, that script is error, so how to convert that script in mysql?

此问题与问题的关系如下：
。

This question relationship with question as follow as: reduction of each row in the table of database.

推荐答案

您需要使用变量来模仿功能。参见以下页面的示例：

You need to use variables to mimic the functionality. See this page for examples:

http://www.onlamp.com/pub/a/mysql/2007/04/12/emulating-analytic- aka-ranking-functions-with-mysql.html？page = 2

-- Oracle
select DEPTNO, AVG(HIRE_INTERVAL)
   2  from  (select DEPTNO,
   3               HIREDATE - LAG(HIREDATE, 1)
   4                             over (partition by  DEPTNO
   5                                   order by HIREDATE)  HIRE_INTERVAL
   6         from EMPLOYEES)
   7   group by DEPTNO

-- MySQL
select DEPTNO, avg(HIRE_INTERVAL)
       -> from (select DEPTNO,
       ->              if (@dept = DEPTNO,
       ->                     datediff(HIREDATE, @hd) +  least(0, @hd := HIREDATE),
       ->                     NULL + least(0, @dept :=  DEPTNO) + (@hd := NULL))
       ->                                                      HIRE_INTERVAL
       ->        from EMPLOYEES,
       ->            (select (@dept := 0)) as a
       ->        order by DEPTNO, HIREDATE) as b
       -> group by DEPTNO;

这篇关于MySQL中的Postgres窗口函数lag（）等效查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！