php - SQL window functions via PHP PDO?

Question

I'm trying to get max, average, and latest values from my table of stuff. Two columns exist that are pertinant: created_at and value. I'm interested in grouping by day, but needing the latest value prevents me from being able to use group by. So I'm using window functions and I have the SQL that works from the MySql client:

select distinct
    DAYOFWEEK(created_at) as dow,
    MAX(value) OVER(PARTITION BY dow ORDER BY created_at) as maxv,
    AVG(value) OVER(PARTITION BY dow ORDER BY created_at) as avgv,
    LAST_VALUE(value) OVER(PARTITION BY dow ORDER BY created_at) as lastv
FROM `insight_follower_counts`;

However, when I try to run this through PHP PDO (actually I'm using Laravel's interface)

$data = DB::select('select distinct ' .
                   'DAYOFWEEK(created_at) as dow, ' .
                   'MAX(value) OVER(PARTITION BY dow ORDER BY created_at) as maxv, ' .
                   'AVG(value) OVER(PARTITION BY dow ORDER BY created_at) as avgv, '.
                   'LAST_VALUE(value) OVER(PARTITION BY dow ORDER BY created_at) as lastv ' .
                   'FROM insight_online_followers');

I get the following error:

Doctrine/DBAL/Driver/PDOException with message 'SQLSTATE[42000]: Syntax error or access violation: 1140 Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause'

Is there a way to do this through PHP PDO? I tried searching but can find nothing related to window functions used via PHP.

标签： phpmysqlsqlwindow-functions