如何在SQL中对指定示例表使用PARTITION BY子句？

Hey there! Let's break down how to use the PARTITION BY clause with your employee event table. First, let's make sure we're all looking at the same data (formatted into a readable table):

1. Propagate block_id to Subsequent 'P' and 'D' Events #

A common task here is filling in missing block_id values for 'P'/'D' events that follow an 'R' event for the same employee. Use PARTITION BY EmpID with LAST_VALUE() to carry the most recent non-null block_id forward:

SELECT
  EmpID,
  Type,
  timestamp,
  LAST_VALUE(block_id IGNORE NULLS) OVER (
    PARTITION BY EmpID
    ORDER BY timestamp
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS filled_block_id
FROM your_table_name;

• PARTITION BY EmpID restricts the window to only events from the same employee.
• ORDER BY timestamp ensures we process events in chronological order.
• IGNORE NULLS skips empty block_id values when grabbing the last valid entry.

2. Count Events per Employee and Type #

If you want to see how many times each event type occurs per employee (without collapsing rows like GROUP BY does), use PARTITION BY EmpID, Type:

SELECT
  EmpID,
  Type,
  timestamp,
  block_id,
  COUNT(*) OVER (
    PARTITION BY EmpID, Type
  ) AS event_count_for_emp_type
FROM your_table_name;

This splits data into groups of unique employee-event type pairs, then adds a column showing the total number of events in that group for every row.

3. Rank Events by Timestamp per Employee #

To assign sequence numbers to events (e.g., first 'R' event, second 'P' event for an employee), combine PARTITION BY with ROW_NUMBER() or RANK():

SELECT
  EmpID,
  Type,
  timestamp,
  block_id,
  ROW_NUMBER() OVER (
    PARTITION BY EmpID
    ORDER BY timestamp
  ) AS overall_event_sequence,
  RANK() OVER (
    PARTITION BY EmpID, Type
    ORDER BY timestamp
  ) AS type_event_sequence
FROM your_table_name;

• overall_event_sequence gives a running number for all events of the employee in time order.
• type_event_sequence ranks each event type separately (e.g., 1st, 2nd, 3rd 'P' event for the employee).

4. Calculate Time Differences Between Consecutive Events #

Use PARTITION BY to find the time gap between an event and the previous one for the same employee:

SELECT
  EmpID,
  Type,
  timestamp,
  block_id,
  TIMESTAMPDIFF(MINUTE, 
                LAG(timestamp) OVER (PARTITION BY EmpID ORDER BY timestamp),
                timestamp) AS minutes_since_last_event
FROM your_table_name;

LAG(timestamp) fetches the timestamp of the prior event for the same employee (thanks to PARTITION BY EmpID), then we calculate the difference in minutes.

内容的提问来源于stack exchange，提问作者Anjali