编写适配Python脚本的SQL查询：基于另一表信息按天条件求和

Got it, let's work through this SQL query step by step. Based on your requirements—daily aggregation of counts from two tables, conditional sums tied to the other table's data, and char-type output for your Python script—here's a flexible solution:

WITH daily_table1 AS (
    SELECT
        DATE(start_time) AS day_date,
        -- Sum counts A only if the 15-minute interval exists in Table2
        SUM(CASE WHEN EXISTS (SELECT 1 FROM Table2 t2 WHERE t2.start_time = t1.start_time) THEN t1.counts_A ELSE 0 END) AS sum_A,
        -- Sum counts B only if the 15-minute interval exists in Table2
        SUM(CASE WHEN EXISTS (SELECT 1 FROM Table2 t2 WHERE t2.start_time = t1.start_time) THEN t1.counts_B ELSE 0 END) AS sum_B
    FROM Table1 t1
    GROUP BY DATE(start_time)
),
daily_table2 AS (
    SELECT
        DATE(start_time) AS day_date,
        -- Sum counts C only if the 15-minute interval exists in Table1
        SUM(CASE WHEN EXISTS (SELECT 1 FROM Table1 t1 WHERE t1.start_time = t2.start_time) THEN t2.counts_C ELSE 0 END) AS sum_C
    FROM Table2 t2
    GROUP BY DATE(start_time)
)
SELECT
    -- Convert date to char, use COALESCE to include days from either table
    CAST(COALESCE(dt1.day_date, dt2.day_date) AS CHAR) AS day,
    -- Convert sums to char, replace NULL with 0 for days with no data
    CAST(COALESCE(dt1.sum_A, 0) AS CHAR) AS total_counts_A,
    CAST(COALESCE(dt1.sum_B, 0) AS CHAR) AS total_counts_B,
    CAST(COALESCE(dt2.sum_C, 0) AS CHAR) AS total_counts_C
FROM daily_table1 dt1
FULL OUTER JOIN daily_table2 dt2 ON dt1.day_date = dt2.day_date
ORDER BY day;

Key Details & Adjustments: #

• Conditional Logic: The CASE WHEN EXISTS clauses enforce that we only sum counts for 15-minute intervals present in the other table. If your condition is different (e.g., based on a third table, a threshold value, or date ranges), modify this part. For example, if you need to sum counts A only where Table2's counts_C > 10, swap the EXISTS check with (SELECT 1 FROM Table2 t2 WHERE t2.start_time = t1.start_time AND t2.counts_C > 10).
• Char Conversion: Every output field is cast to CHAR to match your Python script's needs. Adjust the cast syntax if your SQL dialect uses VARCHAR instead (e.g., PostgreSQL, SQL Server).
• Date Handling: DATE(start_time) extracts the day from the 15-minute datetime. If your SQL dialect uses a different function (like CAST(start_time AS DATE) in SQL Server), swap that out.
• Full Outer Join: Ensures we include all days that have data in either table. COALESCE replaces NULL values with 0 for days where one table has no data.

Testing Tips: #

Run this query against a small sample of your data first to verify the conditional sums are working as expected. Double-check that the char conversion doesn't truncate large numbers (if your counts are very big, use CAST(..., AS VARCHAR(20)) instead to specify a sufficient length).

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