首页 > 其他分享 >lightdb 聚合函数支持order by

lightdb 聚合函数支持order by

时间：2023-11-15 20:44:42浏览次数：30  
标签：count 聚合 lightdb min agg max func test order

在数据库中，对于聚合函数来说，由于不管表中有多少条数据，select 聚合函数始终仅返回一条数据，故对其进行order by是没有意义的，在原生PG中也对该种行为进行了限制：

postgres=# \d t1
Table "public.t1"
Column | Type | Collation | Nullable | Default
--------+----------+-----------+----------+---------
id | integer | | |
ss | tsvector | | |

postgres=#
postgres=# select count(1) from t1 order by id;
2023-11-15 20:21:33.782 CST [77278] ERROR: column "t1.id" must appear in the GROUP BY clause or be used in an aggregate function at character 34
2023-11-15 20:21:33.782 CST [77278] STATEMENT: select count(1) from t1 order by id;
ERROR: column "t1.id" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: select count(1) from t1 order by id;
^
postgres=#

而在oracle中，则支持该种sql的执行：

*可以看出后面的order by也无实际意义

在lightdb 23.4版本oracle兼容模式中，兼容了oracle该种语法（即select语句中仅有聚合函数，对此支持order by语法），具体示例如下：

create table test_agg_func_table(id int primary key, co1 varchar(50), co2 numeric(10, 3));
create table test_agg_func_table2(a int primary key, b varchar(50), c numeric(10, 3), d text);
insert into test_agg_func_table values(1, 'hangzhou', 100.1), (5, 'beijing', 60), (3, 'hangzhou', 45.678);
insert into test_agg_func_table2 values(7, 'hello world', 12.3, '您好'), (500, 'I am fine', 23.45, '北京欢迎你aa'), (13, 'thank you, and you?', 100.1, '我是谁, why, 我在那里');

select id, co1 from test_agg_func_table order by co2;
id | co1
----+----------
3 | hangzhou
5 | beijing
1 | hangzhou
(3 rows)

select count(*) from test_agg_func_table order by id;
count
-------
3
(1 row)

select count(*), id from test_agg_func_table order by id;
ERROR: column "test_agg_func_table.id" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: select count(*), id from test_agg_func_table order by id;
^
select count(*), id from test_agg_func_table order by id, count(*);
ERROR: column "test_agg_func_table.id" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: select count(*), id from test_agg_func_table order by id, co...
^
select count(*) from test_agg_func_table order by count(*);
count
-------
3
(1 row)

select max(co1) from test_agg_func_table order by co2;
max
----------
hangzhou
(1 row)

select count(*), max(co1) from test_agg_func_table order by id;
count | max
-------+----------
3 | hangzhou
(1 row)

select count(*), max(co1), min(co2) from test_agg_func_table order by id;
count | max | min
-------+----------+--------
3 | hangzhou | 45.678
(1 row)

select count(*), max(co1), min(co2), id from test_agg_func_table order by id;
ERROR: column "test_agg_func_table.id" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: select count(*), max(co1), min(co2), id from test_agg_func_t...
^
select count(*), max(co1), min(co2) from test_agg_func_table order by id, co2;
count | max | min
-------+----------+--------
3 | hangzhou | 45.678
(1 row)

select count(*), max(co1), min(co2), co2 from test_agg_func_table order by id, co2;
ERROR: column "test_agg_func_table.co2" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: select count(*), max(co1), min(co2), co2 from test_agg_func_...
^
select count(*), max(co1), min(co2), co2 from test_agg_func_table order by id, co2, count(*);
ERROR: column "test_agg_func_table.co2" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: select count(*), max(co1), min(co2), co2 from test_agg_func_...
^
select count(*), max(co1), min(co2) from test_agg_func_table order by id, co2, count(*);
count | max | min
-------+----------+--------
3 | hangzhou | 45.678
(1 row)

select count(*), max(co1), min(co2) from test_agg_func_table order by count(*), sum(co3);
count | max | min
-------+----------+--------
3 | hangzhou | 45.678
(1 row)


select max(c),count(*),avg(a),sum(c),min(a),stddev(c),variance(c),max(c),count(*),avg(a),sum(c),min(a),stddev(c),variance(c),max(c),count(*),avg(a),sum(c),min(a),stddev(c),
variance(c),max(c),count(*),avg(a),sum(c),min(a),stddev(c),variance(c),max(c),count(*),avg(a),sum(c),min(a),stddev(c),variance(c),max(c),count(*),avg(a),sum(c),min(a),stddev(c),
variance(c),max(c),count(*),avg(a),sum(c),min(a),stddev(c),variance(c),max(c),count(*),avg(a),sum(c),min(a),stddev(c),variance(c),max(c),count(*),avg(a),sum(c),min(a),stddev(c),
variance(c),max(c),count(*),avg(a),sum(c),min(a),stddev(c),variance(c),max(c),count(*),avg(a),sum(c),min(a),stddev(c),variance(c),max(c),count(*),avg(a),sum(c),min(a),stddev(c),
variance(c),max(c),count(*),avg(a),sum(c),min(a),stddev(c),variance(c),max(c),count(*),avg(a),sum(c),min(a),stddev(c),variance(c) from test_agg_func_table2 order by c;
max | count | avg | sum | min | stddev | variance | max | count | avg | sum | min | stddev | variance | max | count | avg | sum | min | stddev | variance | max | count | avg | sum | min | stddev | variance | max | count | avg | sum | min | stddev | variance | max | count | avg | sum | min | stddev | variance | max | count | avg | sum | min | stddev | variance | max | count | avg | sum | min | stddev | variance | max | count | avg | sum | min | stddev | variance | max | count | avg | sum | min | stddev | variance | max | count | avg | sum | min | stddev | variance | max | count | avg | sum | min | stddev | variance | max | count | avg | sum | min | stddev | variance | max | count | avg | sum | min | stddev | variance
---------+-------+----------------------+---------+-----+---------------------+-----------------------+---------+-------+----------------------+---------+-----+---------------------+-----------------------+---------+-------+----------------------+---------+-----+---------------------+-----------------------+---------+-------+----------------------+---------+-----+---------------------+-----------------------+---------+-------+----------------------+---------+-----+---------------------+-----------------------+---------+-------+----------------------+---------+-----+---------------------+-----------------------+---------+-------+----------------------+---------+-----+---------------------+-----------------------+---------+-------+----------------------+---------+-----+---------------------+-----------------------+---------+-------+----------------------+---------+-----+---------------------+-----------------------+---------+-------+----------------------+---------+-----+---------------------+-----------------------+---------+-------+----------------------+---------+-----+---------------------+-----------------------+---------+-------+----------------------+---------+-----+---------------------+-----------------------+---------+-------+----------------------+---------+-----+---------------------+-----------------------+---------+-------+----------------------+---------+-----+---------------------+-----------------------
100.100 | 3 | 173.3333333333333333 | 135.850 | 7 | 47.7988580756207327 | 2284.7308333333333333 | 100.100 | 3 | 173.3333333333333333 | 135.850 | 7 | 47.7988580756207327 | 2284.7308333333333333 | 100.100 | 3 | 173.3333333333333333 | 135.850 | 7 | 47.7988580756207327 | 2284.7308333333333333 | 100.100 | 3 | 173.3333333333333333 | 135.850 | 7 | 47.7988580756207327 | 2284.7308333333333333 | 100.100 | 3 | 173.3333333333333333 | 135.850 | 7 | 47.7988580756207327 | 2284.7308333333333333 | 100.100 | 3 | 173.3333333333333333 | 135.850 | 7 | 47.7988580756207327 | 2284.7308333333333333 | 100.100 | 3 | 173.3333333333333333 | 135.850 | 7 | 47.7988580756207327 | 2284.7308333333333333 | 100.100 | 3 | 173.3333333333333333 | 135.850 | 7 | 47.7988580756207327 | 2284.7308333333333333 | 100.100 | 3 | 173.3333333333333333 | 135.850 | 7 | 47.7988580756207327 | 2284.7308333333333333 | 100.100 | 3 | 173.3333333333333333 | 135.850 | 7 | 47.7988580756207327 | 2284.7308333333333333 | 100.100 | 3 | 173.3333333333333333 | 135.850 | 7 | 47.7988580756207327 | 2284.7308333333333333 | 100.100 | 3 | 173.3333333333333333 | 135.850 | 7 | 47.7988580756207327 | 2284.7308333333333333 | 100.100 | 3 | 173.3333333333333333 | 135.850 | 7 | 47.7988580756207327 | 2284.7308333333333333 | 100.100 | 3 | 173.3333333333333333 | 135.850 | 7 | 47.7988580756207327 | 2284.7308333333333333
(1 row)


select max(x.m) from (select max(id) m, min(co1) n, count(*) from test_agg_func_table order by co2) x order by x.n;
max
-----
5
(1 row)

select max(x.m) from (select max(y.g) m, min(y.h) n, count(*) from (select sum(id) g, count(1) h, min(co1) f from test_agg_func_table order by co2) y order by y.f) x order by x.n;
max
-----
9
(1 row)

select count(*), max(co1) from test_agg_func_table x left join test_agg_func_table2 y on (x.id = y.a) order by x.co1;
count | max
-------+----------
3 | hangzhou
(1 row)

select count(*), max(x.co1), sum(y.c) from test_agg_func_table x left join( select count(a) a, max(b) b, avg(c) c from test_agg_func_table2 order by a) y on (x.id = y.a) order by x.co1;
count | max | sum
-------+----------+---------------------
3 | hangzhou | 45.2833333333333333
(1 row)

 

标签：count,聚合,lightdb,min,agg,max,func,test,order
From： https://www.cnblogs.com/xxl-cr7/p/17834708.html

相关文章

  • First-Order Conditions For Convexity
    StatementoftheFirst-OrderConditionforConvexityForadifferentiablefunction$f:\mathbb{R}^n\to\mathbb{R}$,$f$isconvexonaconvexset$C\subseteq\mathbb{R}^n$ifandonlyifforall$\mathbf{x},\mathbf{y}\inC$thefollowi......
  • mysql中select、from、where、group by、having、order by 、limit执行顺序
    语法顺序：select->from->where->groupby->having->orderby->limit执行顺序：from-->where-->groupby-->having-->select-->orderby-->limit1）from子句组装来自不同数据源的数据；2）使用on进行join连接的数据筛选3）where子句基于指定的条件对记录行进行筛选；4）groupby子......
  • 根据数字值和探究 哈希以及unordered_map实现
    leecode里面的第一题，是两数值和，内容如下/**************************************************************给定一个整数数组nums和一个整数目标值target，请你在该数组中找出和为目标值target的那两个整数，并返回它们的数组下标。你可以假设每种输入只会对应一个答案。......
  • G - Cut and Reorder 状压DP
    我是题目链接首先显然先一操作，然后二操作这样不会影响最终结果一眼状压DP，选出一些a从前往后塞，f[i][j]表示选出的a状态为i，且结尾为j时最小花费转移就看上一个状态的结尾（设为k）和当前结尾（设为j）在a里的下标是否顺着挨着（就是j=k+1），不是顺着挨着就要加个c这样会tle#include<bits/std......
  • MySQL 数据库查询与数据操作：使用 ORDER BY 排序和 DELETE 删除记录
    使用ORDERBY进行排序使用ORDERBY语句按升序或降序对结果进行排序。ORDERBY关键字默认按升序排序。要按降序排序结果，使用DESC关键字。示例按名称按字母顺序排序结果：importmysql.connectormydb=mysql.connector.connect(host="localhost",user="yourusernam......
  • MySQL 数据库查询与数据操作：使用 ORDER BY 排序和 DELETE 删除记录
    使用ORDERBY进行排序使用ORDERBY语句按升序或降序对结果进行排序。ORDERBY关键字默认按升序排序。要按降序排序结果，使用DESC关键字。示例按名称按字母顺序排序结果：importmysql.connectormydb=mysql.connector.connect(host="localhost",user="youruserna......
  • DDD之聚合与聚合根
    目的：高内聚，低耦合，有关系的实体紧密协作，关系很弱的实体被隔离；把关系紧密的实体放到一个聚合中，每个聚合中有一个实体作为聚合根，对所有聚合对象的访问都通过聚合根来进行，外部对象只能持有对聚合根的引用聚合根不仅是实体，还是聚合的管理者；......
  • 指针首汽聚合平台开启招募
    目前，我们将首汽约车代驾项目向全社会公开招募，如果您正在运营一家代驾企业、如果您有资源正在找投资项目、如果您愿意从事代驾行业，我们将与您分享首汽约车品牌价值及流量红利所带来的丰厚收益。品牌优势通过中心化品牌赋能，联合首汽约车品牌，全国范围线上线下齐推广，标准化......
  • ospf 聚合，黑洞，虚连接
    聚合：作用：减少核心骨干的路由条目：提升骨干稳定性1、ABR聚合：对象通过network宣告到OSPF的AS内 优先级为10的路由配置：在ABR上配置，聚合哪个区域就在哪个区域里配，abr-summaryx.x.x.x x.x.x.x2、ASBR聚合：对象是通过import引入到ospf的AS外优先级150的路由配置：在ASBR上配置，在OSPF进......
  • Sql Server 字符串聚合函数
    SqlServer有如下几种聚合函数SUM、AVG、COUNT、COUNT(*)、MAX和MIN，但是这些函数都只能聚合数值类型，无法聚合字符串。如下表:AggregationTableId  Name1  赵2  钱1  孙1  李2  周如果想得到下图的聚合结果Id Name1  赵孙李2  钱周利用SUM、AVG、COUNT......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99