Search code examples
pythonmysqlwindow-functionslead

Cannot Use LEAD in mysql version 8.0.12

I try to lead a column with mysql version 8.0.12 and the code returns an error. Can someone help me ?

Here is my sql configuration

mysql Ver 8.0.12 for osx10.14 on x86_64 (Homebrew)

I have this dataset:

    member_id   date
  0 A           2013-03-29 13:11:19
  1 B           2013-03-29 13:52:41
  2 C           2013-03-29 18:46:12
  3 D           2013-03-30 06:21:50
  4 D           2013-03-30 06:22:13
  5 D           2013-03-30 06:22:21
  6 D           2013-03-30 06:30:51
  7 E           2015-04-30 07:05:53
  8 F           2015-04-30 16:45:57
  9 G           2015-04-30 17:15:14

And I would like to obtain this one 

    member_id   date.                date_led                
  0 A           2013-03-29 13:11:19. NULL
  1 B           2013-03-29 13:52:41. NULL
  2 C           2013-03-29 18:46:12. NULL
  3 D           2013-03-30 06:21:50. 2013-03-30 06:22:13
  4 D           2013-03-30 06:22:13. 2013-03-30 06:22:21
  5 D           2013-03-30 06:22:21. 2013-03-30 06:30:51
  6 D           2013-03-30 06:30:51. NULL
  7 E           2015-04-30 07:05:53. NULL
  8 F           2015-04-30 16:45:57. NULL
  9 G           2015-04-30 17:15:14  NULL

So I did this request in python

connection = pymysql.connect(host='IP',
                         user='NAME',
                         password='PASS',
                         db='DB',
                         charset='utf8mb4',
                         cursorclass=pymysql.cursors.DictCursor
                         )
request = """ SELECT member_id, date, 
   LEAD(date,1) OVER (PARTITION BY member_id ORDER BY date) nextOrderDate
   FROM action_log
"""
pd.read_sql(request, connection)

And I have this error message

ERROR 1064 (42000): 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 member_id ORDER BY date ) nextOrderDate    FROM action_log' at line 1

What am I doing wrong ?

Thanks a lot

Solution

  • It was due to chart.io and windows function.