A Short Story About SQL’s Biggest Rival(holistics.io) |
A Short Story About SQL’s Biggest Rival(holistics.io) |
This is a huge issue with programming in general not exclusive to SQL. Everyone would like to build programs that are modular and reusable but programming paradigms have been traveling in directions that prevent this from happening. Many people turn to design patterns or microservices to try to deal with this organizational issue but they fail to see that the lower level programming paradigm itself is the precursor to the problem.
In SQL the problem occurs in the statement itself. The WHERE clause or the SELECT clause cannot be reused anywhere else. I can't modularize a where clause and put it in another SQL statement. I have to rewrite the entire clause to reuse it.
In OOP the same issue occurs. In OOP your class tends to contain methods that are not combinators, or in other words methods that modify a free variable. Due to this like the SQL expression, Objects cannot be decomposed either. I cannot reuse a setter in another class or anywhere else outside of the context of the free variable it modifies.
In both cases there comes a time in the future of an application where programmers realize that similar logic could be reused but structural problems are preventing the reuse from happening so they have to implement a hack to get around it.
The issue is that everyone is unaware of this trend at a low level. They are unaware that SQL lacks composability just like how they are unaware that OOP lacks composability as well. But they are aware of this issue at a higher level and they tend to call it "technical debt" or some high level design problem.
Case in point: https://news.ycombinator.com/item?id=24732789
Most commenters above talk about minor syntactical issues and fail to address what is not only IMO the main issue, but the main issue that the article itself is addressing. Likely because they're all unaware of the true nature of the composability issue and just didn't completely understand what the article was saying.
Also note that when I talk about composition in OOP I am not talking about "object composition." These are completely different usages of the word.
I want modularity in programs because they are large, and without proper abstraction, impossible to manage.
SQL queries are generally really short. Rarely more than a few lines (might be different for people doing ad-hoc analysis instead of making a db backed application). I don't need modularity for a program that is only a few lines long.
The lack of really natural integration into modern day programming languages (and data model mismatches) is a much bigger issue imo
The SQL data types consist of a few primitives like ints strings and chars placed in higher order data types that are tables. These types are easily isomorphic to data structures and primitive types in traditional programming languages. There is zero mismatch here, in fact the data structures in application programming languages tend to be much richer than SQL.
See orms for reference. ORMS have their own set of problems but data mismatches are not part of those problems. If the programming language enables the creation of sum types like rust or haskell than there can never be a mismatch as these languages can produce virtually any type.
>SQL queries are generally really short. Rarely more than a few lines (might be different for people doing ad-hoc analysis instead of making a db backed application). I don't need modularity for a program that is only a few lines long.
For complex applications this is not true. In general the classic model for web development is to place as much logic as possible into the SQL query and as little logic as possible into your heavy Python web app. The web app is suppose to serve as something that routes IO the bulk of your code/logic and heavy lifting should be shifted to the database. Simple apps can avoid this but in general complex apps cannot.
Case in point do you put your where clause in the application program than download the entire table? Or do you throw as much logic as possible into the query so the database outputs a result as close as possible to what you need? The later statement is the right answer.
>I don't really feel like composability/modularity is all that important in SQL.
You're not completely wrong. The bigger issue is SQL optimization. Because Databases form the backbone of computation for a standard web app SQL provides a layer of indirection that makes optimization harder. For C++ optimization is built into the syntax itself, you make choices while coding to optimize things. For SQL you open up the black box and look into the query planner to see what your High level code is compiling too. SQL is a really bad interface for doing optimizations but that's a topic for another day. The topic of this post is modularity and he's not wrong... SQL is not a composable language and there's no performance loss in making it more composeable.
I have a lot of "where my_func(x, y, z) = 1" type where clauses, so seem that would do what you say, no?
Not even sure if stored procedures are part of the sql standard.... these seem to me to be just specific syntax additions added on by specific databases.
I mean it works so why not. I could code all my SQL this way.
SQL.
There is no reason you couldn't have a composable implementaiton of relational logic.
> The essence of the relational model is that names are known up front
That every relation consist of tuples each member of which consists of a name, a type, and a value is part of the model, sure. That doesn't impose restrictions on composability.
> an attribute of one relation is not an attribute of another.
An attribute of one relation may well be an attribute of another.
There's no reason you couldn't have a relational language that let you store and reuse clauses.
So for example the union operator can take two different relations that have the same columns but different rows and create a new relation.
In this binary operation that takes two different relations.... the attributes of one relation (the columns) is indeed the attributes of the other relation.
See here: https://www.geeksforgeeks.org/basic-operators-in-relational-...
Look for "union" in the link above.
>Union on two relations R1 and R2 can only be computed if R1 and R2 are union compatible (These two relation should have same number of attributes and corresponding attributes in two relations have same domain)
It's the same thing for unary operations like select. A select expression can operate on different relations providing that the relations have the relevant attributes.
Hence why i say why there is a data model mismatch.
E.g. i wouldn't say that assembly and haskell have a compatible data model just because assembly is a sequence of bytes, and haskell is a superset of that.
Not that a data model is solely about the types involved.
> See orms for reference. ORMS have their own set of problems but data mismatches are not part of those problems.
ORMs are notorious for being a leaky abstraction. Largely because the object and relational data model dont entirely match.
> In general the classic model for web development is to place as much logic as possible into the SQL query and as little logic as possible into your heavy Python web app.
When was the last time you wrote a 500 line sql query? A thousand line? My point is that sql queries are short enough, that further abstraction is not really missed. That doesn't mean you should put 0 logic in your query.
> Case in point do you put your where clause in the application program than download the entire table? Or do you throw as much logic as possible into the query so the database outputs a result as close as possible to what you need? The later statement is the right answer.
This is a strawman.
> The bigger issue is SQL optimization. Because Databases form the backbone of computation for a standard web app SQL provides a layer of indirection that makes optimization harder
I disagree. At scale you have data that has mixed cardinality. The indirection allows the db to chose the best algorithm given the size of underlying data at runtime. Sometimes that doesn't work properly, but the vast majority of time it is a significant benefit. Its sort of like how sometimes compilers dont work properly and you need to hand optimize, but in practise that is rare and you wouldn't throw out the compiler because the other 95% of time its better and lower effort than if you had to always do it by hand.
There is no data mismatch if the data model is richer, than it can cover it. Unless you're saying SQL is not rich enough to cover the PL.
Well you can implement a database that does that, it just wouldn't be table based data storage anymore. Postgresql supports JSON and enums if you want it but the minute you use these types things become less predictable in terms of performance.
>ORMs are notorious for being a leaky abstraction. Largely because the object and relational data model dont entirely match.
Wrong ORMs are notoriously leaky because the query translation is extra indirection. You need to compile to SQL and the SQL needs to compile to a query plan. This is why ORMS are bad.
As for the data types, Objects easily mimic types in SQL. You basically rarely ever encounter problems with incompatible type systems.
>When was the last time you wrote a 500 line sql query? A thousand line? My point is that sql queries are short enough, that further abstraction is not really missed. That doesn't mean you should put 0 logic in your query.
When did you last write a 500 line function? When did you last write a 100 line function? You shouldn't be doing that if you are. Case in point, the total lines of SQL written in any source code usually well exceed over 500 and that is a case for composability. T
This is no different than regular source code. If you're writing 500 line functions in your source code than you're not even taking advantage of modularity in programming languages outside of SQL so of course for you it doesn't matter. Because composition doesn't matter for you period.
>This is a strawman.
No it's a statement written in the english language. A strawman is something that doesn't EXIST, because men are made out of flesh not straw.
It's sort of similar to the non-existence of what was suppose to be written in response to my statement: an actual counterpoint or an argument. But then how can you write such a thing if it doesn't exist?
>I disagree. At scale you have data that has mixed cardinality. The indirection allows the db to chose the best algorithm given the size of underlying data at runtime. Sometimes that doesn't work properly, but the vast majority of time it is a significant benefit. Its sort of like how sometimes compilers dont work properly and you need to hand optimize, but in practise that is rare and you wouldn't throw out the compiler because the other 95% of time its better and lower effort than if you had to always do it by hand.
Sure but when it comes time to hand optimize SQL is extremely bad and you need to hand optimize SQL all the time. Not necessarily most of the time, but enough times that it's a real problem. That is the point and that is what I said. It's basically the biggest headache with SQL. If you've ever done analytics you'd know this is a huge problem.
It's better to have a language that allows explicit decorators that allow the programmer to choose optimization procedures when needed. Instead in SQL often optimizations come in the form of hacks. Case in point: SELECT * FROM A is worse then SELECT A.column1 FROM A. The later optimization comes in the form of a language hack and not explicit syntax.
Not saying the alternative mentioned in the article would solve this problem nor am I saying a solution exists... but if there's any big problem with SQL today it's hand optimization for sure.
Hmmm. Not a big lover of SQL, but this is a bit imprecise. While the unit of composability is slightly smaller for from=>where=>select (expression) vs select+from+where (subquery), in practice they both encode the same fundamental compositional principles, based on relational algebra. Any from=>where=>select query can be translated into a select+where+from query almost 1:1, if only via:
from t ::= select * from t
q => where p ::= select * from q where p
q => select xs ::= select xs from q
Quite a few query languages are equally capable (including, surprisingly, quite a few so-called graph languages, provided the SQL dialect provides a transitive closure), but SQL as a language has some undesirable properties (most of which are trade-offs for the fact that basic SQL is very easy to parse).
Was QUEL also more usable and natural to people? As well as being more amenable to composition?
retrieve (a=count(y.i by y.d where y.str = "ii*" or y.str = "foo"), b=max(count(y.i by y.d)))
* Are a and b aggregation functions or window functions? If aggregations, how do they compose if the 'by' scopes are different?
* What does max(count(... by ...)) mean? What is the aggregation (window?) scope of max?
* How would an outer where clause compose? What is the evaluation order?
In which context, "compostable" is a fantastic Freudian typo.
QUEL:
range of E is EMPLOYEE
retrieve into W
(COMP = E.Salary / (E.Age - 18))
where E.Name = "Jones"
select (e.salary / (e.age - 18)) as comp
from employee as e
where e.name = "Jones"
w = employee(name == "Jones")[comp = salary / (age - 18)]
Same problem with declaring imports in javascript. Python got it correct, where I write e.g. 'from somemodule import ...', so by the time I get to the import the parser has enough info to help with autocomplete. Javascript's 'import { Something } from "foo"' means the parser can't help me autocomplete Something.
But, thanks to https://github.com/haskell-beam/beam I no longer need to worry about the COBOL-ness of SQL without giving up the semantics. Finally!
Edit: In case you actually do want to play with it, Ingres is now Actian IngresX. Though I'd recommend thinking about what could have been instead of actually spending any time fighting with and configuring Ingres.
QUEL is still there, but its functionality has been frozen for years and, in terms of bells and whistles, is way behind modern SQL.
If you do want to try it, I had no trouble installing it on my home Ubuntu system a couple of years ago. Ingres is pretty easy to use in some ways. If you want to make a database, you just type "createdb mydatabase" at the command line.
My previous employer used Versant by Actian (now called Actian NoSQL) heavily.
It's much more of a NoSQL database: it's a real object oriented database. You don't store tuples, you store objects. You don't make queries with selection and projection, you make a cut of a graph of objects.
It's insanely fast, multithreaded, has very good tooling, scales vertically very well, can do online schema evolution (class definition evolution, really).
Sadly it's almost impossible to scale horizontally (I'd be glad to be proven wrong).
It's basically what the industry needs to avoid the object-relational mismatch: an object oriented database.
But everybody only learns SQL...
As far I can tell it supports Quel.
Once again, California's absence of noncompetes plays a critical role in the success of a business.
As I said the last time this came around: The end of this isn't quite right. The Postgres project started in 1986. I don't recall what language it used, QUEL perhaps, but it wasn't SQL. SQL support was added between 1994 and 1996, and that's when PostgreSQL was born.
But the ending as it is written seems correct to me.
Some might call the behavior principled, rather than arrogant.
"In this paper, we started with the observation that Datalog engines do not translate across domains. We experimentally evaluated the advantages and disadvantages of existing techniques, and compared them with our own baseline, a general-purpose, parallel, in-memory Datalog solver (RecStep) built upon a rdbms.
"We presented the necessary optimizations and guidelines to achieve efficiency, and demonstrated that RecStep is scalable, applicable to a range of application domains, and is competitive with highly op- timized and specialized Datalog solvers."
vldb.org/pvldb/vol12/p695-fan.pdf (2019)
https://engineering.linkedin.com/blog/2020/liquid-the-soul-o...
https://engineering.linkedin.com/blog/2020/liquid--the-soul-...
People do not know the difference between popularity and merit. They don't realize that the reason we do things is because that is how we do things. And they put a lot of effort into rationalizing the way we do things, without realizing the subconscious psychological (rather than rational) basis for that.
Most people fundamentally are generally unable to question assumptions about technology or how the world works.
This is one reason why, even though I am rooting for the human species, I am doubtful we will be able to stay relevant for long as autonomous general machine intelligence is built and deployed.
Even with extensive augmentation, it's obvious that there are just severe limitations to the human mind.
The nice thing is that we have the opportunity to design successors that will not be limited in so many ways.
https://github.com/Tablam/TablaM
It have ideas similar to QUEL...
https://github.com/prostodata/prosto - Functions matter! No join-groupby, No map-reduce
DBAs of the day would spend a long time optimising physical storage of tables and building aggregation tables to make up for this performance deficit.
MySQL sort of solves this problem with a <=> operator, which I wish was the default for ORMs to use.
There are a lot of other minor nitpicks but a lot of criticisms come down to the actual RDMS not SQL itself.
If we had a more composable query language being used instead of SQL, I wonder if that would have effected the course of ORMs, which arguably end up being as much about composable models of queries as they do about actual object mapping.
And thus PostgreSQL was born."
And 35 years later PostgreSQL is kicking Oracle's butt at every corner.
The best thing about English is the lack of accent marks. Makes each glyph unique (other than casing). Sorts are faster and not ambiguous.
It's good, for some purposes, that English can be written conventionally by ignoring the accent in words such as résumé. But there are plenty of contexts, and I would say most of them, where this is going to bite you.
Bad metaphor: There is no evidence for Dvorak's technical superiority to QWERTY, neither is there for Esperanto over other languages.
One of my major complaints about SQL is the syntax is so finicky that it is really hard to replace it with a [something -> sql] layer, because the something layer can't generate all the silly syntactic forms that SQL uses.
Eg, personal favourite, it is easy to have a dsl that translates
select(y = fn(x)) -> select fn(x) as y
??? -> select extract(month from x) as y
comp = salary / (age - 18)
A better way to formally describe such calculated columns is to introduce functions and treat them as first class elements of the data model. In particular, function operations are better than set operations in these cases [1]:
- Calculating data using calculated columns. We describe a calculated column as a function without generating new relations (as opposed to select-from)
- Aggregating data. We can add an aggregate column without generating new relations (as opposed to groupby)
- Linking data. We can add derived link columns without generating new relations (as opposed to join)
This function-based approach to data modeling and data processing was implemented in Prosto [2] which demonstrates how many typical relational tasks can be solved using functions.
[1] Why functions and column-orientation? https://prosto.readthedocs.io/en/latest/text/why.html
[2] Functions matter! No join-groupby, No map-reduce. https://github.com/prostodata/prosto
Computing is inherently tied to math. There’s no getting away from that.
(and just to be clear, I'm neither for or against)
QUEL:
range of e is employee
retrieve into w (comp = e.salary / (e.age - 18))
where e.name = "Jones"
select
(E.Salary / (E.Age - 18)) as COMP
from EMPLOYEE as E
where E.Name = "Jones"
> To store the results of the retrieve in a new table, specify `into tablename` [1].
matching SQL:
create table w as
select (e.salary / (e.age - 18)) as comp
from employee as e
where e.name = "jones"
I had:
a = employee(name == "Jones") a has name, salary and age
w = a[comp = salary / (age - 18)] w has comp
b = employee[name, comp = salary / (age - 18)] b has name and comp
w = b(name == "Jones" && comp > 500) w has name and comp
w = employee[name, comp = salary / (age - 18)](name == "Jones" && comp > 500) w = employee(name == "Jones")[comp = salary / (age - 18)](comp > 2000)SQL’s shortcomings can be grouped into these categories:
- lack of proper orthogonality — SQL is hard to compose;
- lack of compactness — SQL is a large language;
- lack of consistency — SQL is inconsistent in syntax and semantics;
- poor system cohesion — SQL does not integrate well enough with application languages and protocols.
[1] We Can Do Better Than SQL: https://www.edgedb.com/blog/we-can-do-better-than-sql/
SELECT AVG(col1) OVER (PARTITION BY col2) AS partcol1
FROM tbl
WHERE AVG(col1) OVER (PARTITION BY col2) > 10.0
SELECT AVG(col1) OVER (PARTITION BY col2) AS partcol1
FROM tbl
WHERE partcol1 > 10.0
So if I have a bunch of these and want a convenient way to work with the named columns, I have to wrap them into a common table expression:
WITH cte AS (
SELECT AVG(col1) OVER (PARTITION BY col2) AS partcol1
FROM tbl
)
SELECT partcol1
FROM cte
WHERE partcol1 > 10.0
For example, your example would have been more naturally expressed as
SELECT partcol1
FROM (tbl WITH partcol1 AS AVG(col1) OVER (PARTITION BY col2))
WHERE partcol1 > 10.0WHERE col1 > col2
is wrong, and it'll break in terrible ways
and in the face of negation + NULLs, everything falls apart[0], giving you both false positive and false negatives in your answerset, and everything will look perfectly fine.
Every column being made NOT NULL is the only sane solution.
[0] https://databasetheory.org/sites/default/files/2018-05/03_pr...
That implies that comparison and boolean operators should be ternary, which would be ugly and confusing: but an ugly and confusing that reflects reality.
This would give us three "greater than" operators:
WHERE col1 > col2
WHERE col1 ?> col2
WHERE col1 >? col2
The most important of such operators would be
WHERE col1 ?= col2
FROMCLAUSE * SELECTCLAUSE * WHERECLAUSE = SQLEXPRESSION
SELECTCLAUSE * WHERECLAUSE * FROMCLAUSE = SQLEXPRESSION
...
Example:
select * from customers c inner join orders o on o.order_id = c.customer_id
Legal syntax but clearly incorrect.
[1] https://books.google.co.uk/books?id=t9ZQAAAAMAAJ&source=gbs_...
I agree, and I do wish SQL had stronger typing so the parser could warn you before your query silently runs off the rails. For example, in Oracle, I believe the following is legal, but I wish it wasn’t:
select *
from my tab
where 1 = ‘1’Esperanto isn't intended to be superior. It's value is on it being equally foreign yet approachable for all the salient parties and therefore a conceivable acceptable neutral turf for everyone to share.
Ironically, the case for Dvorak keyboards is kind of the opposite: QWERTY was intentionally designed to avoid jams, which if anything biased towards making it more difficult.
It's not so much technical superiority as having a design objective that is more appropriate for the problem space.
One can similarly argue about whether "QUEL" is really technically superior to "SQL", but the design objective is (at least as perceived by the author) better aligned with the solution space.
Minor quibble that this is not true (or at least strongly disputed).
https://www.smithsonianmag.com/arts-culture/fact-of-fiction-...
This was not even attempted; Esperanto is a Romance language. Unless you think the only salient parties are Spain, France, Portugal, Italy, and Latin America, this "value" does not exist and was not a goal.
Outside Western Europe, Esperanto makes rather little sense. It's highly regular, which is nice — but, say, Japanese is also highly regular.
Today we can be tempted to frame it as european chauvinism, but that's just because of our expanded horizons as a global society. People who believes in esperanto in the early days would likely share the same feeling now
QWERTY isn't as bad as people make it out to be, and Dvorak isn't as good as people make it out to be.
There are keyboard configurations that are better than both, but nobody uses them because you'll never be able to use anybody else's keyboard.
Proprietary software is a cancer.
QUEL is better there but still not great unless its execution semantics are different than SQL's (aka semantically does it filter before or after selection?)
I ask because I work on Crux [0] which at its core is a point-in-time bitemporal Datalog engine, and I see a lot of similarities (schemaless core, Worst-Case Optimal Join etc.)
SELECTCLAUSE(WHERECLAUSE(relation)) = relation
WHERECLAUSE(SELECTCLAUSE(relation)) = relation
My example in the previous post has a flaw where it's not clear what:
SELECTCLAUSE * WHERECLAUSE = ????
I doubt that's the main reason, and it may be unwarranted as I say below. Personally, I thought it would take too long for me to be able to type as fast as in QWERTY. Fortunately, I was wrong about that too.
I learned the QGMLW optimized layout from Carpalx (linked above) about a year ago. I trained on https://keybr.com and then on https://typeracer.com for a few days and in less than a week I reached 70wpm. I stopped training shortly after and now I can usually reach 90-100wpm. Not particularly fast, but I'm not any faster in QWERTY, and I believe I could get faster if I trained more.
Even though I almost never type in QWERTY anymore, when I do I'm still just as fast as I used to be. I never learned to type in QWERTY "the right way", though, and I suppose this is actually what keeps me from confusing the two "modes". Whenever I try to keep my fingers on their "appropriate" keys (like a properly trained typist would), it's like my brain switches to "Carpalx mode" (since I did make an effort to use the right finger positioning to learn it). It's kinda like switching between thinking in my native language and English - I can think in both, but it doesn't "feel" the same, and I'm more likely to confuse the two where they overlap more. Pretty interesting, really.
I can still type qwerty but I'm not very fast at it (I never was).
People always rationalize their inability to change. Those who made a step knows both rewards and cost. It was worth it hundred times.
"The world has since standardised on SQL, and the dreams of an alternate history exists only in the heads of those who had a hand in the early database wars. It was simply a quirk of history that System R was built within IBM, the single most powerful company in the computer industry at the time; it was a quirk that the engineers who built System R came up with a fiddly language interface as an afterthought, and it was a quirk that IBM then took that language and pushed it to become a standard … one that has lasted till today.
Of course, there was a silver lining to the whole saga. Stonebraker had forked the Ingres codebase in 1982 to create his company. Defeated by the bruising database wars of the 80s, he returned to Berkeley in 1985, and started a post-Ingres database project. Naturally, he named that database post-gres — as in, after Ingres.
And thus PostgreSQL was born."
I was trying to figure what you meant. I think I have an inkling now -- let me know if this is correct. Your quibble is with the fact the implication here is that since SQL won, Stonebraker jumped on the SQL bandwagon and created a SQL database, when in fact, he didn't -- he merely created Postgres, which ran on QUEL and didn't have SQL until much later.
I think the author made a stylistic choice to omit that detail to drive home a point, but even so nothing was said that was non-factual.
... he returned to Berkeley in 1985, and started a post-
Ingres database project.
Naturally, he named that database post-gres — as in, after Ingres.
And thus PostgreSQL was born.
I personally think eliding details was artistic license to make the prose flow better without bringing in ancillary details, but that's just me.
Stonebraker did eventually change his mind about SQL however -- if you've watched any of his recent talks he's of the opinion that most query languages will eventually and inexorably converge to some variant of SQL. (he was wrong about Mongo inventing a SQL-like query language, but that's what his philosophical commitments look like these days)
> count() Number of entries in column
> max() Maximum value in column
> The by clause causes the function to return a set of results, as opposed to a single result. One result is returned for each grouping specified by the by clause. Think of by as meaning "for each."
I assume it evaluates like retrieving set and scalar.
a | b
--------------
set 1 | scalar
set 2 | scalar
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 # 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)
# with bar as (select count(y.i), y.d from y group by y.d)
select *, (select count(*) from bar) as bar from foo;And it was pretty much a goal of its creator to be both familiar and foreign for different european language families, as he had experienced division in Poland between speakers of those different families and wanted to have something more universal to gather them all together.
Esperanto has been described as "a language lexically predominantly Romanic, morphologically intensively agglutinative, and to a certain degree isolating in character".[74] Typologically, Esperanto has prepositions and a pragmatic word order that by default is subject–verb–object. Adjectives can be freely placed before or after the nouns they modify, though placing them before the noun is more common. New words are formed through extensive prefixing and suffixing."
# 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)top3 - max 3 values in the group