如何通过SQL查询为日期列添加循环？附指定ID交易数据整理需求

Alright, let's tackle your two SQL questions one by one—happy to help break this down with practical examples tailored to common use cases!

First, let's clarify what "adding a loop to a date column" usually means—it typically falls into two common scenarios, so I'll cover both:

Scenario 1: Generate a repeating/looping date sequence #

If you need to create a sequence of dates that repeats on a cycle (e.g., every weekday, or the 1st of every month), a recursive CTE is your go-to tool. Here's an example for generating 10 weeks of repeating Monday-to-Friday dates:

WITH RECURSIVE date_loop AS (
  -- Start with your initial date range (Mon-Fri of the first week)
  SELECT DATE '2024-01-01' AS loop_date, 1 AS week_num
  UNION ALL
  -- Add 7 days each iteration to repeat the weekly cycle
  SELECT 
    loop_date + INTERVAL '7 days',
    week_num + 1
  FROM date_loop
  WHERE week_num < 10 -- Stop after 10 cycles
)
SELECT loop_date FROM date_loop
ORDER BY loop_date;

Adjust the interval (e.g., INTERVAL '1 month' for monthly cycles) or initial date range to match your needs. Note: Syntax varies slightly by SQL dialect—MySQL uses WITH RECURSIVE, SQL Server uses WITH, and date functions like DATEADD might replace + INTERVAL.

Scenario 2: Apply loop logic to an existing date column #

If you need to iterate over existing dates (e.g., add 3 days to each transaction date until you hit the end of the month), another recursive CTE works here. Example for a specific user's transaction dates:

WITH RECURSIVE user_dates AS (
  SELECT 
    user_id,
    transaction_date AS current_date,
    1 AS loop_count
  FROM transactions
  WHERE user_id = '01234'
  UNION ALL
  SELECT 
    user_id,
    current_date + INTERVAL '3 days',
    loop_count + 1
  FROM user_dates
  -- Stop when the next date would exceed the current month's last day
  WHERE current_date + INTERVAL '3 days' <= DATE_TRUNC('month', current_date) + INTERVAL '1 month - 1 day'
)
SELECT user_id, current_date, loop_count FROM user_dates;

Since you mentioned "specified format," I'll cover two common formatting needs—adjust based on your exact desired output:

Option 1: Aggregate transactions into a single row (comma-separated values) #

This works if you want to combine all 3 transaction details into one line per month:

SELECT
  user_id,
  DATE_TRUNC('month', transaction_date) AS transaction_month,
  COUNT(*) AS total_transactions,
  -- Merge dates into a comma-separated string
  STRING_AGG(transaction_date::TEXT, ', ') AS transaction_dates,
  -- Merge amounts the same way
  STRING_AGG(CAST(amount AS TEXT), ', ') AS transaction_amounts
FROM transactions
WHERE user_id = '01234'
GROUP BY user_id, DATE_TRUNC('month', transaction_date)
HAVING COUNT(*) = 3; -- Only keep months with exactly 3 transactions

• For MySQL: Replace STRING_AGG with GROUP_CONCAT
• For SQL Server: Use STRING_AGG (2017+) or STUFF + FOR XML PATH for older versions

Option 2: Pivot transactions into structured columns (Transaction 1, 2, 3) #

If you want each transaction as a separate column (e.g., trans1_date, trans2_amount), use window functions to rank transactions first, then pivot with conditional aggregation:

WITH ranked_transactions AS (
  SELECT
    user_id,
    transaction_date,
    amount,
    DATE_TRUNC('month', transaction_date) AS transaction_month,
    -- Rank transactions by date within each month
    ROW_NUMBER() OVER (PARTITION BY user_id, DATE_TRUNC('month', transaction_date) ORDER BY transaction_date) AS trans_rank
  FROM transactions
  WHERE user_id = '01234'
)
SELECT
  user_id,
  transaction_month,
  MAX(CASE WHEN trans_rank = 1 THEN transaction_date END) AS trans1_date,
  MAX(CASE WHEN trans_rank = 1 THEN amount END) AS trans1_amount,
  MAX(CASE WHEN trans_rank = 2 THEN transaction_date END) AS trans2_date,
  MAX(CASE WHEN trans_rank = 2 THEN amount END) AS trans2_amount,
  MAX(CASE WHEN trans_rank = 3 THEN transaction_date END) AS trans3_date,
  MAX(CASE WHEN trans_rank = 3 THEN amount END) AS trans3_amount
FROM ranked_transactions
GROUP BY user_id, transaction_month
HAVING COUNT(*) = 3;

If you need JSON output instead, use functions like JSON_AGG (PostgreSQL), JSON_ARRAYAGG (MySQL), or FOR JSON PATH (SQL Server) to wrap the transaction details into a JSON array.

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