On Wed, Mar 8, 2017 at 3:47 PM, Clemens Ladisch <clem...@ladisch.de> wrote:
> Dominique Devienne wrote:
> > On Wed, Mar 8, 2017 at 3:16 PM, Hick Gunter <h...@scigames.at> wrote:
> >> Alternatively create a "range" table, insert your defined ranges and
> >> join/subquery to the original query.
> >
> > That's the easy part [...]
> > but the join/subquery you mention is still leaving me scratching my
> head...
>
> WITH u(v) AS (
> SELECT sum(s)
> FROM t
> GROUP BY p
> )
> SELECT ranges.name,
> u.v
> FROM u
> JOIN ranges ON u.v >= ranges.low
> AND u.v < ranges.high;
>
> name v
> ------------- ------
> [0, 1KB) 904
> [8KB, 64KB) 36000
> [64KB, 512KB) 370368
>
Thank you Clemens. I wasn't familiar with joins on range conditions like
this.
I needed count aggregates, so I moved the query into another CTE, and
grouped that.
Then the results where in alphabetical order of bucket names,
so I had to re-join on ranges to order by ranges.low.
Then I changed the join into a left-join to see all ranges, even empty ones.
And finally I trimmed empty ranges at the top and bottom, leaving
only the "middle" holes, as I think it's more natural that way.
Now I only need a CTE to dynamically generate the ranges,
but I'll call it a day and this will be for another time.
Here's my final query (on a slightly modified schema). --DD
with
bytes_per_set(v) as (
select sum(byte_size)
from values
group by parent, set
),
minmax(a, b) as (
select min(v), max(v) from bytes_per_set
),
buckets(name, v) as (
select ranges.name, bytes_per_set.v
from bytes_per_set
join ranges ON bytes_per_set.v >= ranges.low
AND bytes_per_set.v < ranges.high
),
distrib(b, c) as (
select name, count(*)
from buckets
group by 1
)
select ranges.name as "size range",
distrib.c as '#sets'
from ranges
left join distrib on ranges.name = distrib.b
where ranges.low < (select b from minmax)
and ranges.high > (select a from minmax)
order by ranges.low
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
