Where does WINDOW go with JOIN and WHERE clauses?-CodePudding

In the MySQL documentation, there's this example:

SELECT
     time, subject, val,
     FIRST_VALUE(val)  OVER w AS 'first',
     LAST_VALUE(val)   OVER w AS 'last',
     NTH_VALUE(val, 2) OVER w AS 'second',
     NTH_VALUE(val, 4) OVER w AS 'fourth'
FROM observations
WINDOW w AS (PARTITION BY subject ORDER BY time
             ROWS UNBOUNDED PRECEDING);

That's all good, but my query has multiple JOIN and some conditions in a WHERE clause. For example, for this query :

SELECT *
FROM (
   SELECT
      edpe.id,
      edpe.noIdentEvent,
      rpe.noIdentPersonnel,
      rpi.employeeBadge,
      edpe.deviceId,
      ed.deviceName,
      rpe.statut AS punchStatus,
      edpe.status AS processStatus,

      ROW_NUMBER() OVER(PARTITION BY rpe.noIdentPersonnel ORDER BY edpe.createdAt ASC) AS _rowCount,
      
      edpe.createdAt,
      edpe.processedAt
   FROM `em_device_punch_events` edpe
   CROSS JOIN `rh_punch_event` rpe ON edpe.NoIdentEvent = rpe.NoIdent
   CROSS JOIN `rh_personel_ident` rpi ON rpi.NoIdent = rpe.NoIdentPersonnel
   CROSS JOIN `em_devices` ed ON ed.id = edpe.deviceId
   WHERE edpe.createdAt >= DATE(NOW() - INTERVAL (WEEKDAY(NOW())   1) MOD 7 DAY)
   ORDER BY rpe.noIdentPersonnel, edpe.createdAt ASC
) e
WHERE e.processedAt IS NULL
AND e.noIdentPersonnel = 650;

Where would the WINDOW clause go in order to have

SELECT *
FROM (
   SELECT
      edpe.id,
      edpe.noIdentEvent,
      rpe.noIdentPersonnel,
      rpi.employeeBadge,
      edpe.deviceId,
      ed.deviceName,
      rpe.statut AS punchStatus,
      edpe.status AS processStatus,

      ROW_NUMBER() OVER employeeWindow AS _rowCount,
      
      edpe.createdAt,
      edpe.processedAt
   FROM `em_device_punch_events` edpe
   CROSS JOIN `rh_punch_event` rpe ON edpe.NoIdentEvent = rpe.NoIdent
   CROSS JOIN `rh_personel_ident` rpi ON rpi.NoIdent = rpe.NoIdentPersonnel
   CROSS JOIN `em_devices` ed ON ed.id = edpe.deviceId
   WHERE edpe.createdAt >= DATE(NOW() - INTERVAL (WEEKDAY(NOW())   1) MOD 7 DAY)
   ORDER BY rpe.noIdentPersonnel, edpe.createdAt ASC
) e
WHERE e.processedAt IS NULL
AND e.noIdentPersonnel = 650;

-- ??? WINDOW employeeWindow AS (PARTITION BY rpe.noIdentPersonnel ORDER BY edpe.createdAt ASC)

CodePudding user response：

WINDOW goes between HAVING and ORDER BY because it's the last combinable clause; the rest of them (starting at ORDER BY) are not combinable.

The typical sequence is: SELECT, FROM, JOIN/LATERAL, WHERE, GROUP BY, HAVING, WINDOW, ORDER BY, LIMIT, OFFSET.

In your case the WINDOW clause should be placed inside the subquery at:

WHERE edpe.createdAt >= DATE(NOW() - INTERVAL (WEEKDAY(NOW())   1) MOD 7 DAY)
WINDOW employeeWindow AS ...
ORDER BY rpe.noIdentPersonnel, edpe.createdAt ASC