1

I try to run the query below but a syntax error occurs. I checked many times how to write a last value window function correctly but still there must be a mistake that I cannot see. I tried to add three different kind of frame_clause but none of them solved the syntax error. Please, help me to figure it out.

SQL:

select DATE_FORMAT(Day, "%Y-%m") as Months,
SiteId,
ShopId,
Sum(Clicks) as Montly_Clicks,
Sum(Spending) as Montly_Spending,
last_value(Sum(Clicks)) OVER (
PARTITION BY ShopId
ORDER BY DATE_FORMAT(Day, "%Y-%m") desc
-- range between current row and unbounded following
-- ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
-- RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) as LastMonth_Clicks
from
bi_test3.SourceTable
Where SiteId = 103 
and DATE_FORMAT(Day, "%Y-%m") between '2016-04' and '2018-09'
Group by 
DATE_FORMAT(Day, "%Y-%m"),
ShopId
Order by
DATE_FORMAT(Day, "%Y-%m"),
ShopId

Sample from the table:

Id  Day         ShopId  SiteId  Clicks  Spending
16  2018-10-28  5255294 101     186     0.008420
17  2018-10-28  5251217 101      84     0.024163
18  2018-10-28  5252073 101     979     0.006105
19  2018-10-28  5256205 101     171     0.008790

Error message says the problem is near to Partition by

One more thing which might relate to the problem that in my query last_value and over text is not highlighted with colour. Maybe it's a problem because commands, operators, function etc. are always highlighted with different colours in a query. I use MySQL Workbench 8.0.22

Create table script:

CREATE TABLE `SourceTable` (
  `Id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Primary key of table',
  `Day` date NOT NULL COMMENT 'Day of item.',
  `ShopId` int(10) unsigned NOT NULL COMMENT 'Shop identifier',
  `SiteId` int(10) unsigned NOT NULL COMMENT 'Site identifier',
  `Clicks` int(10) unsigned NOT NULL COMMENT 'Total paid and free clicks on a given day.',
  `Spending` decimal(14,6) DEFAULT NULL COMMENT 'Total sepnding on a given day.',
  PRIMARY KEY (`Id`),
  KEY `Day` (`Day`),
  KEY `ShopId` (`ShopId`),
  KEY `SiteId` (`SiteId`)
) ENGINE=InnoDB AUTO_INCREMENT=4478684 DEFAULT CHARSET=utf8

server verson: mysql Ver 8.0.22 for Win64 on x86_64 (MySQL Community Server - GPL)

Bendegúz Tunyogi
  • 33
  • 3
  • A more simple query also doesn't work: select Day, SiteId, ShopId, Clicks, Spending, last_value(Clicks) OVER ( PARTITION BY ShopId ORDER BY Day desc from bi_test3.SourceTable – Bendegúz Tunyogi Nov 24 '20 at 17:15
  • So that's why I think that there must be a mistake in the window function itself – Bendegúz Tunyogi Nov 24 '20 at 17:17
  • Provide complete CREATE TABLE script and precise server (not client!) version. – Akina Nov 24 '20 at 17:21
  • Done! Sorry, it's my first time – Bendegúz Tunyogi Nov 24 '20 at 18:02
  • Not reproduced. [fiddle](https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=d7d6e12932e9756715bc1b19b4d08a5b) - no syntax error. Check one more time - do you access correct server? the error message looks like you try to use aggregate function as window one on version 5.x. – Akina Nov 24 '20 at 18:06
  • It's a trial server database for a job application to complete analytical tasks. Do you think it's a problem that I want to use a sum function being part of the window fuction? A more simplier query without sum and group by does not working as well. So that's why I thought there must be a problem with the window function somehow – Bendegúz Tunyogi Nov 25 '20 at 09:34
  • It's interesting that on fiddle it's not reproducible... maybe there are some problems with the server as you mentioned... a more specific error message would be helpful – Bendegúz Tunyogi Nov 25 '20 at 09:38
  • I had an ideal to dump the database then import it to a localhost and when I started the export process a warning message popped up: the version of the server and the client is not the same. Server version is: 5.7.30. Does the windows function not working because of the older version of the server? – Bendegúz Tunyogi Nov 25 '20 at 09:47
  • *Server version is: 5.7.30. Does the windows function not working because of the older version of the server?* Of course, window functions were implemented at MySQL version 8.0. – Akina Nov 25 '20 at 10:01
  • I see.. then we found the problem. Thanks for your help :) I exported the db to a dump file and I will try to import it to localhost. I hope it will resolve the problem – Bendegúz Tunyogi Nov 25 '20 at 10:34

0 Answers0