PostgreSQL中如何实现产品数据的月度日期拆分与逐日输出

Great question! To expand each product's record to cover every day from its starting date through the end of the target month, PostgreSQL's generate_series function is perfect for this task—it lets you generate a sequence of dates easily.

Step-by-Step Solution #

First, let's break down the core logic:

1. For each product in your product table, grab its original starting date.
2. Generate all dates from that starting date to the last day of the month matching the starting date.
3. Combine this date sequence with the product's details, creating a new sequential ID (or keep the original product ID if that fits your needs better).

Example SQL Query #

Here's the query that will deliver your desired output, and it works for all products in the table:

SELECT
    ROW_NUMBER() OVER (ORDER BY p.name, generated_date) AS id,
    p.name,
    generated_date AS date
FROM
    product p
CROSS JOIN LATERAL
    generate_series(
        p.date,
        (date_trunc('month', p.date) + INTERVAL '1 month' - INTERVAL '1 day')::DATE,
        INTERVAL '1 day'
    ) AS generated_date
-- Optional: Uncomment below to filter for a specific month (e.g., January 2021)
-- WHERE date_trunc('month', p.date) = '2021-01-01'::DATE
ORDER BY
    p.name,
    generated_date;

Let's Break Down the Query #

• generate_series: This function creates a date sequence starting from the product's original date, ending on the last day of that month. We calculate the last day by first getting the first day of the month with date_trunc('month', p.date), adding a full month, then subtracting one day.
• CROSS JOIN LATERAL: This ensures we generate a unique date sequence for each product, so every product gets its own set of dates from its start to the month's end.
• ROW_NUMBER(): Generates a new sequential ID for each row in the expanded result (matching your example). If you want to keep the original product ID repeated for every date in its sequence, replace ROW_NUMBER() OVER (...) with p.id.

Sample Output #

Using your original product data, this query would produce results like:

Customization Tips #

• If you want to force all products to cover a specific fixed month (e.g., January 2021 regardless of their original start date), adjust the generate_series parameters to hardcoded dates:

  generate_series(
      '2021-01-01'::DATE,
      '2021-01-31'::DATE,
      INTERVAL '1 day'
  )
  

• If you don't need a new sequential ID, just use p.id to retain the original product ID for every row in its expanded date series.

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