基于BigQuery递归SQL计算项目进度的开始与结束时间

Alright, let's solve this recursive project scheduling problem in BigQuery. You need to calculate start and finish dates for each activity based on their predecessors, following specific rules—here's a complete solution tailored to your requirements.

Input Activity Data #

First, let's structure your raw input into a clear table for reference:

Expected Calculated Schedule #

The final output you're aiming for (with dates formatted for readability):

Recursive BigQuery SQL Solution #

This query uses BigQuery's WITH RECURSIVE syntax to iterate through dependencies, resolve dates, and stop once all activities have finalized start/finish values:

WITH RECURSIVE activities AS (
  -- Step 1: Prep raw data, convert P columns to an array for flexible predecessor handling
  SELECT
    id,
    name,
    duration,
    -- Combine non-null predecessor IDs into a single array
    ARRAY_CONCAT(
      IF(p1 IS NOT NULL, [p1], []),
      IF(p2 IS NOT NULL, [p2], []),
      IF(p3 IS NOT NULL, [p3], [])
    ) AS predecessors,
    -- Initialize dates for activities with no predecessors
    CASE WHEN p1 IS NULL AND p2 IS NULL AND p3 IS NULL THEN DATE('2017-01-01') ELSE NULL END AS start_date,
    CASE WHEN p1 IS NULL AND p2 IS NULL AND p3 IS NULL THEN DATE_ADD(DATE('2017-01-01'), INTERVAL (duration - 1) DAY) ELSE NULL END AS finish_date
  FROM
    -- Replace this with your actual BigQuery table reference
    `your-project.your-dataset.your-table`
),
recursive_schedule AS (
  -- Base case: Start with activities that have no dependencies (dates already known)
  SELECT * FROM activities WHERE start_date IS NOT NULL
  UNION ALL
  -- Recursive step: Calculate dates for activities where all predecessors are processed
  SELECT
    a.id,
    a.name,
    a.duration,
    a.predecessors,
    -- Start date = max of 2017-01-01 and (latest predecessor finish + 1 day)
    GREATEST(
      DATE('2017-01-01'),
      DATE_ADD(MAX(rs.finish_date), INTERVAL 1 DAY)
    ) AS start_date,
    -- Finish date = start date + duration - 1 days
    DATE_ADD(
      GREATEST(
        DATE('2017-01-01'),
        DATE_ADD(MAX(rs.finish_date), INTERVAL 1 DAY)
      ),
      INTERVAL (a.duration - 1) DAY
    ) AS finish_date
  FROM
    activities a
  JOIN
    recursive_schedule rs ON rs.id IN UNNEST(a.predecessors)
  -- Only process activities where all predecessors are already in the recursive schedule
  WHERE
    a.start_date IS NULL
    AND (SELECT COUNT(*) FROM UNNEST(a.predecessors) p WHERE p NOT IN (SELECT id FROM recursive_schedule)) = 0
  GROUP BY
    a.id, a.name, a.duration, a.predecessors
)
-- Final output: Format dates to match your expected style, and include original P columns
SELECT
  rs.id,
  rs.name,
  rs.duration,
  FORMAT_DATE('%d-%b-%y', rs.start_date) AS START,
  FORMAT_DATE('%d-%b-%y', rs.finish_date) AS FINISH,
  a.p1,
  a.p2,
  a.p3
FROM
  recursive_schedule rs
JOIN
  `your-project.your-dataset.your-table` a ON rs.id = a.id
ORDER BY
  rs.id;

How This Works #

Let’s break down the logic to make it clear:

1. Base CTE (activities): We transform the raw input to store predecessors as an array (this handles variable numbers of dependencies, not just 1-3). We also set initial dates for activities with no predecessors, since their start is fixed to 2017-01-01.
2. Recursive CTE (recursive_schedule):
   • Base Case: Starts with all activities that have no dependencies—their dates are already finalized.
   • Recursive Step: For each activity with unknown dates, we check if all its predecessors have been processed. If yes, we calculate the start date as the later of 2017-01-01 or the latest predecessor's finish date plus one day. The finish date is derived by adding the duration (minus one day) to the start date.
3. Final Output: We join back to the original table to include the original P1/P2/P3 columns, format dates to match your desired readability, and sort by activity ID.

Key Notes #

• Replace your-project.your-dataset.your-table with your actual BigQuery table path.
• This solution scales if you add more predecessor columns later—just update the ARRAY_CONCAT section to include the new columns.
• The recursion stops automatically once all activities have calculated dates, as there will be no more unprocessed activities left.

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