3 ms·
Assuming we key a and b by y.d and generalizing b to sets, we are reaching the limits of the relational model: there is no good way to represent multiple sets a
by alquemist 6y ago
Assuming we key a and b by y.d and generalizing b to sets, we are reaching the limits of the relational model: there is no good way to represent multiple sets associated with a given key, we need independent tables to do so. Neither cross product nor null padding is a good way to represent the schema {y, a[], b[]}.
y.d | a | b
-------------------
y0 | a0 | b0
y0 | a0 | b1
y0 | a1 | b0
y0 | a1 | b1
y0 | a2 | b0
y0 | a2 | b1
y.d | a | b
-------------------
y0 | a0 | b0
y0 | a1 | b1
y0 | a2 | null
- sergeykish 6y ago# create table y (i int, d int); # insert into y values (1, 1), (1, 2), (2, 1); # select *, (select count(*) from (select count(y.i), y.d from y group by y.d) _) as foo from (select count(y.i), y.d from y group by y.d) _ ; count | d | foo -------+---+----- 1 | 2 | 2 2 | 1 | 2 (2 rows) By the way great example how unwieldy SQL is. A bit better with CTE: # with bar as (select count(y.i), y.d from y group by y.d) select *, (select count(*) from bar) as bar from foo;
- alquemist 6y agoNice! Should be easy to do max instead of count. Then top instead of max. Then top3 breaks the model.
- sergeykish 6y agoYes, should be max(count). I've lost you with top3. # select * from (select count from (select count(y.i), y.d from y group by y.d) _ limit 2) b, (select count(y.i), y.d from y group by y.d) a; count | count | d -------+-------+--- 1 | 1 | 2 2 | 1 | 2 1 | 2 | 1 2 | 2 | 1 (4 rows)
- alquemist 6y agotop - max value in the group top3 - max 3 values in the group