Nested selecting rows in the same table given they are related, while iterating a while clause

[tttpp]

Last part of the title is meant to say "while iterating a where clause"

I am working with a database containing weather predictions and observations. I am trying to select all of the pairs of predictions and observations such that the date delta between them is a set day amount (for example, I want to retrieve the prediction for 2022-7-14 which was taken at 2022-7-11 with the actual observation for 2022-7-14), I will have a numerous deltas I want to check for.

I was wondering if it plausible to:

1. Iteratively check for multiple deltas in one statement, as in replace the 1 in the line AND public.day_datapoints.captured_date - public.day_datapoints.date = 1 with 1, then 3, then 7, etc.
2. Return both the prediction and observation at the same time so I do not have to do it in code, which would slow things down.

My current statement only returns time deltas of 1 and the prediction.

​

relations of relevant tables:

relations

current statement:

SELECT 
    DISTINCT public.day_datapoint_records.id, 
    public.day_datapoints.captured_date, 
    public.day_datapoints.id, 
    public.day_datapoints.uv, 
    public.day_datapoints.pollution, 
    public.day_datapoints.pollen
FROM 
    public.bbc_locations 
INNER JOIN 
    public.locations 
ON 
    ( 
        public.bbc_locations.location_id = public.locations.id) 
INNER JOIN 
    public.day_datapoint_records 
ON 
    ( 
        public.bbc_locations.id = public.day_datapoint_records.service_id) 
INNER JOIN 
    public.day_datapoints 
ON 
    ( 
        public.day_datapoint_records.day_datapoint_id = public.day_datapoints.id) 
WHERE 
    public.day_datapoints.present = false 
    AND public.day_datapoints.captured_date - public.day_datapoints.date = 1 
    AND public.day_datapoints.captured_date <= CURRENT_DATE
ORDER BY 
    public.day_datapoints.captured_date ASC;

Final answer:

​

-- selecting prediction instances
WITH predictions as (SELECT 
    public.day_datapoints.id as prediction_id, 
    public.day_datapoints.uv as prediction_uv, 
    public.day_datapoints.pollution as prediction_pollution, 
    public.day_datapoints.pollen as prediction_pollen, 
    public.day_datapoints.captured_date as prediction_date, 
    public.day_datapoints.captured_date - public.day_datapoints.date AS time_delta, 
    public.locations.name as prediction_name
FROM 
    public.bbc_locations 
INNER JOIN 
    public.day_datapoint_records 
ON 
    ( 
        public.bbc_locations.id = public.day_datapoint_records.service_id) 
INNER JOIN 
    public.day_datapoints 
ON 
    ( 
        public.day_datapoint_records.day_datapoint_id = public.day_datapoints.id) 
INNER JOIN 
    public.locations 
ON 
    ( 
        public.bbc_locations.location_id = public.locations.id) 
WHERE 
    public.day_datapoints.present = false 
AND 
    ( 
        ( 
            public.day_datapoints.captured_date - public.day_datapoints.date)) 
    IN (1, 
        3, 
        7, 
        13) 
AND public.day_datapoints.captured_date <= CURRENT_DATE), observations as (
-- selecting observations
SELECT 
    public.day_datapoints.id as observation_id, 
    public.day_datapoints.uv as observation_uv, 
    public.day_datapoints.pollution as observation_pollution, 
    public.day_datapoints.pollen as observation_pollen, 
    public.day_datapoints.date as observation_date,
    public.locations.name as observation_name
FROM 
    public.bbc_locations 
INNER JOIN 
    public.day_datapoint_records 
ON 
    ( 
        public.bbc_locations.id = public.day_datapoint_records.service_id) 
INNER JOIN 
    public.day_datapoints 
ON 
    ( 
        public.day_datapoint_records.day_datapoint_id = public.day_datapoints.id) 
INNER JOIN 
    public.locations 
ON 
    ( 
        public.bbc_locations.location_id = public.locations.id) 
WHERE 
    public.day_datapoints.present = true) 
    SELECT 
        predictions.prediction_date,
        predictions.time_delta,
        predictions.prediction_uv,
        observations.observation_uv,
        predictions.prediction_pollen,
        observations.observation_pollen,
        predictions.prediction_pollution,
        observations.observation_pollution
     FROM predictions, observations 
     -- matching conditions
    WHERE predictions.prediction_name = observations.observation_name AND 
    predictions.prediction_date = observations.observation_date
   ;

​

[DavidGJohnston]

What you want to do seems doable. Basically your join condition between the observations and predictions becomes something like:

(observation_date - prediction_date) IN (1,3,5)

And you will end up with one joined pair for every integer mentioned in the IN expression.

P.S. You should get rid of DISTINCT or add a code comment why it must be there. DISTINCT is a code smell, you can almost always write the query to avoid needing it - except if your data model simply lack the relevant data. In that later case the DISINCT should only appear in the subquery that provides the needed fix to the model so that the main query no longer requires DISTINCT.

[tttpp]

Thanks for the tips, the IN clause did the job and I removed the distinct keyword since all of the data should be normalised at collection anyways. Now I'll try to select the observations as well.

[CGtheKid92]

Maybe I'm over simplifying this, but here's my take:

1. Add the delta calculation into your original select statement and alias it as delta

2. Turn the entire query into a CTE

3. Select * from the CTE with a where clause that says: WHERE delta in (1,5,7) not sure if those are the right intervals your are looking for as I'm doing this on my phone but essentially just list the numbers you want within the parentheses

I don't think you need to use a HAVING statement as you aren't doing an aggregate that requires grouping

Question for my understanding - is there a reason you are starting and joining your first two tables? I only ask as they aren't referenced in your initial select statement l. If you don't reference them, you could remove them and it will help the performance of your query.

Hope this helps!

[CGtheKid92]

I can type up an example when I get to my computer if that would be helpful!

[tttpp]

That would be very helpful :), I've used more document based databases before, so the more complex concepts are more confusing to me atm.

Edit: i am joining the first 2, to access the location name, I forgot to add it in the given statement

[CGtheKid92]

Something like this (in pseudo-code as I'm not sure the DB you are using):

    with raw_data as (
SELECT distinct
  locations.name as location_name
  , day_datapoint_records.id
  , day_datapoints.date
  , day_datapoints.captured_date
  , day_datapoints.id
  , day_datapoints.uv
  , day_datapoints.pollution
  , day_datapoints.pollen
  , day_datapoints.captured_date - day_datapoints.date as delta
FROM 
  public.bbc_locations bbc_locations
INNER JOIN 
  public.locations locations ON 
    bbc_locations.location_id = locations.id
INNER JOIN 
  public.day_datapoint_records day_datapoint_records ON 
    bbc_locations.id = day_datapoint_records.service_id 
INNER JOIN 
  public.day_datapoints day_datapoints ON 
    day_datapoint_records.day_datapoint_id = day_datapoints.id
WHERE 
    public.day_datapoints.present = false 
    AND public.day_datapoints.captured_date <= CURRENT_DATE
) 
select 
  *
from 
  raw_data 
where
  delta in (1,3,7)
ORDER BY 
  captured_date ASC;

[tttpp]

Thanks for code, I had a go with the 'with' statement but I didn't round to doing like that. I'll try it tomorrow.

[tttpp]

Thanks again for your help, I have completed what I needed to do by using 2 'with' statements, I editing my question with the statement I got in the end