3

There are two table below, project_check_history is a history of some checked did on a project and project_check_metrics is that table holding the metrics collected about that project.

Tables and sample data:

CREATE TABLE project_check_history (
  id serial PRIMARY KEY,
  project_id int4,
  check_type varchar,
  checked_at timestamp
);

CREATE TABLE project_check_metrics (
  id serial PRIMARY KEY,
  project_id int4,
  metrics json,
  created_at timestamp
);

INSERT INTO project_check_history(project_id, check_type, checked_at) 
  VALUES 
      (1, 'instant', '2016-09-25'),
      (1, 'scheduled', '2016-09-24'),
      (1, 'scheduled', '2016-09-23');

INSERT INTO project_check_metrics(project_id, metrics, created_at) 
  VALUES 
      (1, '{ "name": "10" }', '2016-09-25'),

      (1, '{ "name": "3" }', '2016-09-24'),
      (1, '{ "name": "5" }', '2016-09-24'),
      (1, '{ "name": "1" }', '2016-09-24'),

      (1, '{ "name": "3" }', '2016-09-23'),
      (1, '{ "name": "2" }', '2016-09-23'),
      (1, '{ "name": "1" }', '2016-09-23');

Also available on SQLFiddle

Using that data I am trying to build a query that will return this output:

project_id, checked_at, metrics,       check_type
1,          2016-09-25, {'name': '10'}, instant
1,          2016-09-25, {'name': '3'},  instant
1,          2016-09-25, {'name': '5'},  instant
1,          2016-09-25, {'name': '1'},  instant
1,          2016-09-24, {'name': '3'},  scheduled
1,          2016-09-24, {'name': '5'},  scheduled
1,          2016-09-24, {'name': '1'},  scheduled
1,          2016-09-23, {'name': '3'},  scheduled
1,          2016-09-23, {'name': '2'},  scheduled
1,          2016-09-23, {'name': '1'},  scheduled

Inside project_check_history there is a row with a instant check_type, in those situations I need to look inside the table and find the closest date to 2016-09-25 which is of scheduled check_type that will be 2016-09-24, once we fond that date, I want to bring all the data found inside project_check_metrics for both dates(2016-09-25, 2016-09-24), and associate that data with the 2016-09-25 which is the date from project_check_history.

Update

Here is my attempt at creating this query

SELECT pch.project_id
     , pch.checked_at::date
     , min(pch.check_type) AS check_type
     , array_agg(metrics)
     , count(*)
FROM project_check_history pch
INNER JOIN project_check_metrics pcm
        ON pcm.project_id = pch.project_id
WHERE (pch.check_type = 'instant' AND pcm.created_at::date 
        IN (
                SELECT checked_at 
                FROM project_check_history 
                WHERE checked_at <= pch.checked_at 
                LIMIT 2
            )
        ) 
    OR ( pch.check_type = 'scheduled' AND
         pcm.created_at::date = pch.checked_at::date)
GROUP BY pch.project_id, pch.checked_at::date
ORDER BY 2 DESC

The above almost works, I still have a problem with my condition

SELECT checked_at 
FROM project_check_history 
WHERE checked_at <= pch.checked_at 

what I would like to do here is to SELECT all the checked_at where check_type is instant but stop as soon as I find check_type schedule and return N rows containing instant where the checked_at < pch.checked_at and just one row containing check_type schedule where checked_at < pch.checked_at

Expected output from the above data set checked_at: 2016-09-25, 2015-09-24

Please let me know if you see a better way of creating this query.

Update

Solution for subquery here

Update

Running this query on big data sets does not perform :(

  • 2
    Thanks for providing the script for the tables and the sample data. It is however better to include that in your question as well, just in case SQLFiddle is not reachable (which happens more often then not) – a_horse_with_no_name Sep 26 '16 at 12:20
  • Will the row with scheduled always be before the one with instant? And why are you using a timestamp if you are casting that to a date all the time? – a_horse_with_no_name Sep 26 '16 at 21:04
  • Something like this maybe? sqlfiddle.com/#!15/2d01a/3 – a_horse_with_no_name Sep 26 '16 at 21:10
  • @a_horse_with_no_name i can change it to a date, here is a solution to my problem, sqlfiddle.com/#!15/2d01a/9, but it does not work when I have a lot of metrics – alexsmn Sep 27 '16 at 7:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.