如何计算每日开放的公开披露请求数？SQL查询报错求助

Hey there! Let's get your SQL query sorted out so you can count how many public disclosure requests were open on each date. That "unknown table 'd'" error usually pops up because you're referencing a date alias (d) that hasn't been properly defined in your query—let's fix that and build the right query for your use case.

The Core Logic You Need #

To count open requests on a given date, you need to:

• Generate a complete sequence of dates covering your entire dataset (from the earliest request creation date to the latest close date, or today if there are still open requests).
• For each date in that sequence, count all requests where:
  1. The request was created on or before the date
  2. The request was closed after the date (or never closed, i.e., closed_date is null)

Working SQL Query #

Assuming your table is named public_disclosure_requests with columns created_date (request creation date) and closed_date (request close date), here's a query that will work:

-- First, generate a full range of dates to check
WITH date_range AS (
    SELECT generate_series(
        -- Start at the earliest request creation date
        (SELECT MIN(created_date) FROM public_disclosure_requests),
        -- End at the latest close date (or today if there are open requests)
        (SELECT COALESCE(MAX(closed_date), CURRENT_DATE) FROM public_disclosure_requests),
        INTERVAL '1 day'
    ) AS stat_date
)
SELECT
    dr.stat_date::DATE AS date,
    COUNT(pdr.id) AS open_requests
FROM date_range dr
LEFT JOIN public_disclosure_requests pdr
    ON pdr.created_date::DATE <= dr.stat_date::DATE
    AND (pdr.closed_date::DATE > dr.stat_date::DATE OR pdr.closed_date IS NULL)
GROUP BY dr.stat_date::DATE
ORDER BY dr.stat_date::DATE;

Breaking Down the Query #

• CTE date_range: This creates every single date in your dataset's time frame using generate_series (compatible with PostgreSQL, which data.world uses).
• Left Join: We connect each date in our range to the requests that were open on that date. The join conditions strictly enforce your "open request" rules.
• Group & Count: Finally, we group by each date and count the matching requests to get your daily open request count, sorted chronologically.

Why You Got the "unknown table 'd'" Error #

Chances are your original query tried to reference a date alias d without first creating that date sequence (like the date_range CTE above). The database didn't recognize d because it wasn't defined as a table or CTE in your query—this fix explicitly generates the dates you need to check, eliminating the error.

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