SQL技术需求：筛选日期生成两两组合表并随源表自动更新

Got it, let's walk through how to build exactly what you need— a dynamic, auto-updating set of date pairs with day differences, based on filtered values from your Data table.

Key Approach: Use a View (Not a Physical Table) #

Since you want the new table to sync automatically when Data updates, a database view is perfect here. Views are virtual tables that pull data in real-time from the source table(s) every time you query them, so no manual refreshes needed.

Step-by-Step Implementation #

1. Define Your Filter Condition

First, we need to filter the dates from Data based on your numeric column. Let's assume your numeric column is named daily_value (replace this with your actual column name). For example, if you want dates where daily_value is greater than 50, your filter would be WHERE daily_value > 50. Adjust this to match your specific criteria.

2. Create the View with Date Pairs & Day Differences

We'll use a self-join on the filtered dates to get all possible pairs, then calculate the day difference between each pair. Here's the code:

-- Replace with your actual column names and filter condition
CREATE VIEW Date_Pairs_With_Diffs AS
SELECT
  d1.date AS date_1,
  d2.date AS date_2,
  -- Adjust DATEDIFF syntax based on your SQL database (see notes below)
  DATEDIFF(d2.date, d1.date) AS day_difference
FROM
  (SELECT DISTINCT date FROM Data WHERE daily_value > 50) AS d1
CROSS JOIN
  (SELECT DISTINCT date FROM Data WHERE daily_value > 50) AS d2
-- Optional: Exclude pairs where date_1 equals date_2 (if you don't need zero-day differences)
WHERE
  d1.date != d2.date;

Notes on DATEDIFF Syntax

Different SQL databases use slightly different syntax for calculating day differences:

• MySQL/MariaDB: DATEDIFF(d2.date, d1.date) returns date_2 - date_1 as the number of days.
• SQL Server: DATEDIFF(day, d1.date, d2.date) (you need to specify the interval as day).
• PostgreSQL: d2.date - d1.date directly gives the day difference as an integer, or use DATE_PART('day', d2.date - d1.date) for explicit formatting.

Customization Options #

• Avoid duplicate pairs: If you don't want both (dateA, dateB) and (dateB, dateA) in your results, change the WHERE clause to d1.date < d2.date instead of d1.date != d2.date.
• Handle duplicate dates: The SELECT DISTINCT date ensures we don't get duplicate rows if your Data table has multiple entries for the same date. If each date is unique in Data, you can remove DISTINCT.

How to Use the View #

Once the view is created, you can query it just like a regular table:

SELECT * FROM Date_Pairs_With_Diffs;

Any updates to the Data table (new rows, changed values, deleted rows) will automatically be reflected the next time you query this view.

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