0

I save visits to my database. In my database I have a table with aggregations of visits called visits_agg. This table has one row per hour per hostname. Both are fields in the table which will be used to query the rows needed for viewing statistics.

For example I will query for the visits in the last 24 hours:

SELECT * FROM visits_agg WHERE hostname = 'example.com' AND hour > NOW() - INTERVAL '24 hours'

To create the structure I used this query:

CREATE TABLE IF NOT EXISTS visits_agg (
  id          serial primary key,
  hour        timestamp without time zone default (now() at time zone 'utc'),
  hostname    text not null,
  visits      integer default 0,
  referrers   jsonb not null default '{}'::jsonb,
  paths       jsonb not null default '{}'::jsonb,
  widths      jsonb not null default '{}'::jsonb
);

With some dummy data:

INSERT INTO "visits_agg" ("hour", "hostname", "visits", "referrers", "paths", "widths") VALUES
('2019-01-16 09:00:00', 'example.com', '6', '{"example2.com": 4}', '{"/": 5, "/about": 1}', '{"1280": 5, "1283": 1}'),
('2019-01-16 14:00:00', 'example.com', '7', '{"google": 6}', '{"/": 1, "/about": 3, "/article/title1": 1, "/article/title2": 2}', '{"360": 3, "1398": 1, "1680": 1, "2560": 1}'),
('2019-01-16 15:00:00', 'example.com', '6', '{"google": 6}', '{"/about": 1, "/article/title1": 1, "/article/title2": 3, "/": 1}', '{"360": 3, "1280": 2, "1920": 1}'),
('2019-01-16 16:00:00', 'example.com', '6', '{"facebook": 1, "example2.com": 1}', '{"/": 4, "/about": 1, "/article/title1": 1}', '{"360": 1, "1440": 2, "1680": 2, "1920": 1}');

I would like to get the rows from a certain period and sum all referrers, paths, and widths.

So the result of the above records should be something like this

'example.com',
'25',
'{"example2.com": 4, "google": 12, "facebook": 1}',
'{"/": 11, "/about": 6, "/article/title1": 3, "/article/title2": 5}',
'{"360": 7, "1398": 1, "1440": 2, "1680": 3, "2560": 1, "1280": 7, "1283": 1, "1920": 2}'),

The keys in the jsonb objects can be any string and the values are always numbers. I tried to merge the objects, but can't figure out how to sum the values inside the jsonb objects.

  • Is there one disaggregated table with this information? – McNets Jan 16 at 21:09
  • I do have access to the disaggregated table which has the following structure: id, url (contains the path), referrer, hostname, added, extra (jsonb field containing the width). The reason why I have this aggregated table is performance. I have millions of records with visits and sometimes need queries for the last 3 months. It's very expensive to get that data directly from the disaggregated table. I'm using database triggers to populate the aggregated table. I'm open to other solutions. – adriaan Jan 17 at 11:22
  • I could also sum those numbers in the application code, but that still means to I need to get 2160 records out of the database for 3 months of data. I could also make a aggregated table with daily views. – adriaan Jan 17 at 11:24
  • Let me know if this answer is acceptable to you. – McNets Jan 17 at 11:55
  • @McNets having 4 subqueries is not really something I'm looking for. I appreciate the effort and yes I was asking for a solution for reading out my hourly aggregates table. I think I'm going to use a cronjob which will add hourly to a daily aggregates table which I'm going to use for fast loading my data. Unless there is a better way. – adriaan Jan 18 at 7:03
0

As I pointed out in comments I think this job would be easy to accomplish working with a disaggregated data, because now IMHO you must disaggregate it again:

For example to get aggregated values for referrers:

SELECT 
    hostname,
    r.key as ref_name,
    r.value::int as ref_value
FROM
    visits_agg
CROSS JOIN LATERAL
    jsonb_each(referrers) r;
hostname    | ref_name     | ref_value
:---------- | :----------- | --------:
example.com | example2.com |         4
example.com | google       |         6
example.com | google       |         6
example.com | facebook     |         1
example.com | example2.com |         1

Now you can get acumulated values grouped by ref_name, and then you must build the jsonb array again.

SELECT   hostname, jsonb_agg(jsonb_build_object(ref_name, ref_value)) as referrers
FROM     (SELECT hostname, r.key as ref_name, SUM(r.value::int) as ref_value
          FROM   visits_agg
          CROSS JOIN LATERAL jsonb_each(referrers) r
          GROUP BY hostname, r.key) t1
GROUP BY hostname;

This is my approach, perhaps some Postgres guru can give you a better answer.

SELECT row_to_json(t0, true)
FROM
    (
    SELECT t1.hostname, t1.visits, t2.referrers, t3.paths, t4.widths
    FROM   (SELECT   hostname, SUM(visits) as visits
            FROM     visits_agg
            GROUP BY hostname) t1
    JOIN   (SELECT   hostname, jsonb_agg(jsonb_build_object(ref_name, ref_value)) as referrers
            FROM     (SELECT hostname, r.key as ref_name, SUM(r.value::int) as ref_value
                      FROM   visits_agg
                      CROSS JOIN LATERAL jsonb_each(referrers) r
                      GROUP BY hostname, r.key) t1
           GROUP BY hostname) t2
           ON t1.hostname = t2.hostname
    JOIN   (SELECT   hostname, jsonb_agg(jsonb_build_object(path_name, path_value)) as paths
            FROM     (SELECT hostname, p.key as path_name, SUM(p.value::int) as path_value
                      FROM   visits_agg
                      CROSS JOIN LATERAL jsonb_each(paths) p
                      GROUP BY hostname, p.key) t1
            GROUP BY hostname) t3
           ON t1.hostname = t3.hostname
    JOIN   (SELECT   hostname, jsonb_agg(jsonb_build_object(width_name, width_value)) as widths
            FROM     (SELECT hostname, w.key as width_name, SUM(w.value::int) as width_value
                      FROM   visits_agg
                      CROSS JOIN LATERAL jsonb_each(widths) w
                      GROUP BY hostname, w.key) t1
            GROUP BY hostname) t4
           ON t1.hostname = t4.hostname
    ) t0;

And this is the result:

{
 "hostname":"example.com",
 "visits":25,
 "referrers":[{"google": 12}, {"facebook": 1}, {"example2.com": 5}],
 "paths":[{"/about": 6}, {"/article/title2": 5}, {"/article/title1": 3}, {"/": 11}],
 "widths":[{"1680": 3}, {"1398": 1}, {"2560": 1}, {"360": 7}, {"1280": 7}, {"1440": 2}, {"1920": 2}, {"1283": 1}]
}

db<>fiddle here

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.