Are there any SQL-standard or DBMS implementation that offers "sample aggregator" for predictably repeated columns?

I will explain, see bold text at the end.

PS: this question is about "little performance gain" in big tables, and about "semantic enhancements" in the SQL language, in general.

As typical table, we can imagine a SQL-view v1 of some piece of data:

Organization   | City           |  Country | Info1 | Info2
---            | ---            | ---      | ---   | ---
LocalOrg1      | San Francisco  |  US      | 10    | 23
LocalOrg1      | San Francisco  |  US      | 2     | 24
Armarinhos Fer | São Paulo      |  BR      | 11    | 55
Armarinhos Fer | São Paulo      |  BR      | 12    | 56

My (programmer's) prediction is that all organizations of v1 exists in only one city (eg. there are only one "San Francisco City Hall"). So,on basis of my prediction, the query

SELECT organization, city, country, 
       sum(info1) as tot1, avg(info2) as avg2, count(*) as n
GROUP BY organization, city, country  -- city,country predicted repeat

have some redundancy and some semantic limitation: the grouping criteria is organization, there is no need to spend CPU checking city and country. And I can say "Hey John look that query grouped by organization", because it does not make sense to say to another human "organization, city and country" when he have the same prediction in mind. It was a SQL obligation but is not the semantic essence of the query.

Of course, the semantic may be better with GROUP by organization, 2,3 syntax sugar, or using max(),

SELECT organization, max(city) as city, max(country) as country, 
       sum(info1) as tot1, avg(info2) as avg2, count(*) as n
GROUP BY organization  -- better semantic, worse performance 

but max() lost time "comparing nothing" (because as I predicted there are only repeated things), and perhaps the query cost will be bigger than the first query.

Some DBMS also offer first() and last() which might be more efficient (!) then max(), and I guess that this is the simplest solution today: use some kind of first() function optimize performance and semantic, in basis of my prediction that city and country repeats when grouping by organization.

But no matter if the sample of a repeated column comes from first or last sampled row. The first/last can be also an internal optimization choice, so, the real need in this context is a kind of aggsample() function:

SELECT organization, 
       aggsample(city), aggsample(country) -- better semantic and perfornace
       sum(info1) as tot1, avg(info2) as avg2, count(*) as n
GROUP BY organization  -- better performance 

So, putting in this detailed context: Are there are any SQL language variation where this kind of function (aggsample) was defined?

EDITED after comments and some homework...


Candidates and limitations:

  • PostgreSQL's DISTINCT ON. Seems to address the similar problem, but not solves the use in usual GROUP BY summarizations.

  • MySQL's ANY_VALUE, as suggested by @AndriyM. Seems perfect (!), but I never used... Will try later, install mysql and do some tests.

Similar discussions:


  Organization text,  City text,
  Country text, Info1  int, Info2 int
 ('LocalOrg1', 'San Francisco', ' US', '10  ', '23'),
 ('LocalOrg1', 'San Francisco', ' US', '2   ', '24'),
 ('Armarinhos Fer', 'São Paulo', ' BR', '11  ', '55'),
 ('Armarinhos Fer', 'São Paulo', ' BR', '12  ', '56');

Extrange, PostgreSQL offer as valid syntax "DISTINCT ON + GROUP BY",

SELECT DISTINCT ON (organization) organization, city, country, 
       sum(info1) as tot1, avg(info2) as avg2, count(*) as n
GROUP BY organization, city, country

but not works better (worst performance) and is not valid the reduction ou grouping with only GROUP BY organization.

... no other test.

PS: the only valid syntax (and result) with DISTINCT ON of postgresql is

SELECT DISTINCT ON (organization) organization, City, Country, 
    sum(Info1) OVER w AS tot1,
    avg(info2) OVER w AS avg2,
    count(*)   OVER w as n
WINDOW w AS (PARTITION BY organization);

as we see, ugly syntax, and with worst performance.

  • You seem to be talking about MySQL GROUP BY.
    – Andriy M
    Mar 29 '16 at 8:21
  • That said, I'm not sure if it makes sense at all to pick a SQL variation (and, consequently, a SQL product) based on one specific feature – especially taking into account that you aren't actually speaking about a real performance problem, you are just making guesses. They look sound, I give you that, but the issue may not necessarily be big enough to worry about it prematurely. Perhaps some measurements/comparisons could clear that up.
    – Andriy M
    Mar 29 '16 at 8:57
  • Thanks @AndriyM, your clue about MySQL was perfect (see my edit), the MySQL's ANY_VALUE() seems my aggsample(). About "pick a SQL variation", my aim is not a SQL product choice, is only to check "sucess experiences" in this feature, and perhaps to show a reference model to other SQL user-community (ex. PostgreSQL users). Mar 29 '16 at 17:54
  • I wonder if TABLESAMPLE is what you are looking for: postgresql.org/docs/current/static/sql-select.html and postgresql.org/docs/current/static/tsm-system-rows.html Mar 29 '16 at 18:13
  • Thanks @a_horse_with_no_name, is a clue, but no... It is used for statistical sampling purposes, and act in whole row. Eg. TABLESAMPLE BERNOULLI (50) is like WHERE random()<0.5;, but with "randomness at the page level instead of the tuple level" 1. Mar 29 '16 at 18:38

Thanks @AndriyM, the link to this new features in MySQL 5.7 led me to the ANY_VALUE() function, that is exactly the "aggsample" of my question!

  • Performance conclusion: the use of ANY_VALUE() function represents some gain in performance, demonstrated by thousands of real world use cases of MySQL databases, and by the decision to preserve this advantage in nowadays versions of MySQL, that bring us with this new function.

  • Usability (friendliness) conclusion: ANY VALUE() makes possible semantic gain (takeoff the non-grouping columns) in the GROUP BY clause.

  • Philosophical conclusion: it is way to programmer express his/her predictions about repeated values.

so, ANY VALUE() is a good recommendation for any other SQL language!


To test you need MySQL 5.7+, and check SELECT @@GLOBAL.sql_mode or set mode to ONLY_FULL_GROUP_BY.

The query works fine,

SELECT organization, any_value(city) AS city, any_value(country) as country,
       sum(info1) as tot1, avg(info2) as avg2, count(*) as n 
FROM v1 GROUP BY organization;

Another interesting behaviour in MySQL is let user in a friendly mode, where any_value() is not necessary — so, is also possible to avoid "AS name" boring syntax. In this special (non-standard) mode, the following query have the same behaviour,

SELECT organization, city, country,
       sum(info1) as tot1, avg(info2) as avg2, count(*) as n 
FROM v1 GROUP BY organization;
  • to install mysql5.7 in ubuntu wget http://dev.mysql.com/get/mysql-apt-config_0.6.0-1_all.deb\n sudo dpkg -i mysql-apt-config_0.3.2-1ubuntu14.04_all.deb\n sudo apt-get update\n sudo apt-get install mysql-server-5.7 Mar 30 '16 at 3:01

Frame challenge: you wouldn't have this problem if you normalized your database properly. Therefore, the problem should have been solved at the design stage and you wouldn't need to invent semantically ambiguous aggregate functions to work around it.

If the combination of Organization, Info1, and Info2 is guaranteed to be unique (which I'm pretty sure is not the case), then these attributes form a composite candidate key. Subsequently, your relation is not in the 2NF, as City and Country are only functionally dependent on a subset of the candidate key (Organization), and need to be in a different relation.

If the combination of Organization, Info1, and Info2 is not guaranteed to be unique, then this relation is completely unnormalized (i.e. is not indeed a relation) and you need to start your design from scratch.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.