Question 396667 in Ubuntu Question posted in
The official Ubuntu documentation can be found here.

Ubuntu – Get totals for top 5 elements in GROUP BY, and then the rest of the groups in a single total

I have a question. I have the following scheme

CREATE TABLE 'redirect3' (
  id INT,
  short_url_id INT,
  browser SYMBOL capacity 256 CACHE,
  platform SYMBOL capacity 256 CACHE,
  os SYMBOL capacity 256 CACHE,
  referrer_domain VARCHAR,
  country SYMBOL capacity 256 CACHE,
  language SYMBOL capacity 256 CACHE,
  time TIMESTAMP
) timestamp (time) PARTITION BY MONTH WAL;

You can generate some pseudo-random data for this table like this:

INSERT INTO redirect3 (
    id, 
    short_url_id, 
    browser, 
    platform, 
    os, 
    referrer_domain, 
    country, 
    language, 
    time
)
SELECT
    x AS id,
    rnd_int(1, 100, 0) AS short_url_id,
    rnd_symbol('Chrome', 'Firefox', 'Safari', 'Edge', 'Opera', 'Internet Explorer', 'Brave') AS browser,
    rnd_symbol('Windows', 'Mac', 'Linux', 'Android', 'iOS') AS platform,
    rnd_symbol('Windows 10', 'macOS', 'Ubuntu', 'Android 11', 'iOS 14') AS os,
    rnd_str('google.com', 'facebook.com', 'twitter.com', 'linkedin.com', 'instagram.com', 'youtube.com', 'reddit.com') AS referrer_domain,
    rnd_symbol('US', 'UK', 'DE', 'FR', 'JP', 'IN', 'BR') AS country,
    rnd_symbol('en', 'es', 'de', 'fr', 'ja', 'hi', 'pt') AS language,
    timestamp_sequence(to_timestamp('2023-01-01', 'yyyy-MM-dd'), 1000000000) AS time -- 1 second intervals
FROM long_sequence(1000, 123456789, 987654321) x;

For example, I would now like to display the top 5 browsers and then others with the remaining values.

I see 2 options for this

I use SELECT count(), browser FROM redirect3 and truncate and sum after the 5th value.

I fetch the top 5 and the total number.

With the pseudo random data above, the results should be

Browser Total
-Others- 256
Firefox 160
Internet Explorer 152
Edge 152
Safari 141
Chrome 139

I would actually like to do the same for other fields like os and country.

Does questdb allow me to do all of this in a query or how would you implement it?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    We can do something like this (this query can be executed at the demo instance https://demo.questdb.io)

    with totals AS 
(
select 
symbol
, count() as total from trades
), ranked AS (
SELECT *, rank() over (order by total DESC) as ranking from totals
)
select case when ranking <= 5 THEN 
symbol
 else '-Others-' end, SUM(total)
from ranked order by 2 DESC;

    For the table in the question, it would be

    with totals AS 
(
select 
browser
, count() as total from redirect3
), ranked AS (
SELECT *, rank() over (order by total DESC) as ranking from totals
)
select case when ranking <= 5 THEN 
browser
 else '-Others-' end, SUM(total)
from ranked order by 2 DESC;

  2. 0

    You can try this:

        WITH browser_counts AS (
    SELECT 
        browser, 
        count() AS total 
    FROM 
        redirect3 
    GROUP BY 
        browser
),
top_browsers AS (
    SELECT 
        browser, 
        total 
    FROM 
        browser_counts 
    ORDER BY 
        total DESC 
    LIMIT 
        5
),
other_browsers AS (
    SELECT 
        '-Others-' AS browser, 
        sum(total) AS total 
    FROM 
        browser_counts 
    WHERE 
        browser NOT IN (SELECT browser FROM top_browsers)
)
SELECT * FROM top_browsers
UNION ALL
SELECT * FROM other_browsers;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search