I have the following table "numbers" in my MariaDB database:

Now I would like to create a view that lets me see:

• column 1: All possible numbers (distinct)
• column 2: the amount of occurrences of that number
• column 3: the amount of IDs between the last ID and the ID from that number.

That would result in this view:

So I started with:

SELECT DISTINCT number FROM numbers 

and then a left join on the same table to count , but that makes the query verrrry slow (and it didn't work the way I wanted). Do you have any idea how to resolve this? Thanks a lot in advance!

CodePudding user response：

Yo can group by number and use COUNT() aggregate function to get the column occurrences and MAX() window function to get the column IDdifferences:

SELECT number,
       COUNT(*) occurrences,
       MAX(MAX(id)) OVER () - MAX(id) IDdifferences
FROM numbers
GROUP BY number
ORDER BY number;

If there are gaps between the ids:

SELECT number,
       COUNT(*) occurrences,
       MAX(MAX(rn)) OVER () - MAX(rn) IDdifferences
FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY id) rn FROM numbers) t
GROUP BY number
ORDER BY number;

See the demo.