-
oracle的 listagg() WITHIN GROUP () 行转列函数的使用
2018-07-11 18:12:25如需转载请标明出处 1.使用条件查询 查询部门为20的员工列表 -- 查询部门为20的员工列表 SELECT t.DEPTNO,t.ENAME FROM SCOTT.EMP t where t.DEPTNO = '20' ; 效果: ...2.使用 listagg() WITHIN GROUP () 将多行...如需转载请标明出处
1.使用条件查询 查询部门为20的员工列表
-- 查询部门为20的员工列表 SELECT t.DEPTNO,t.ENAME FROM SCOTT.EMP t where t.DEPTNO = '20' ;
效果:
2.使用 listagg() WITHIN GROUP () 将多行合并成一行(比较常用)
SELECT T .DEPTNO, listagg (T .ENAME, ',') WITHIN GROUP (ORDER BY T .ENAME) names FROM SCOTT.EMP T WHERE T .DEPTNO = '20' GROUP BY T .DEPTNO
效果:
3. 使用 listagg() within GROUP () over 将多行记录在一行显示(没有遇到过这种使用场景)
SELECT T .DEPTNO, listagg (T .ENAME, ',') WITHIN GROUP (ORDER BY T .ENAME) over(PARTITION BY T .DEPTNO) FROM SCOTT.EMP T WHERE T .DEPTNO = '20'
效果:
注:使用的表数据是oracle 用户scott下的emp(员工)表
mysql相同效果的实现 https://blog.csdn.net/sinat_36257389/article/details/95052001
PostgreSQL 相同效果的实现 https://blog.csdn.net/sinat_36257389/article/details/95611686
-
PostgreSQL中的crosstab(行转列函数)
2019-05-27 12:35:43PostgreSQL中的crosstab(行转列函数) 行转列是一个非常有用的功能,如果不适用行转列函数,则通用做法是使用 case when 。不过,接下来,介绍一下今天的新主角–crosstab 一、安装扩展 环境:centos7 、pg10(yum ...PostgreSQL中的crosstab(行转列函数)
行转列是一个非常有用的功能,如果不适用行转列函数,则通用做法是使用 case when 。不过,接下来,介绍一下今天的新主角–crosstab
一、安装扩展
环境:centos7 、pg10(yum 安装)、扩展组件 tablefunc
crosstab
需要按照扩展tablefunc
[root@uzong ~]# su postgres bash-4.2$ psql technology postgres could not change directory to "/root" psql (9.2.24, server 10.8) WARNING: psql version 9.2, server version 10.0. Some psql features might not work. Type "help" for help. technology=# create extension tablefunc; CREATE EXTENSION technology=#
二、 使用入门
2.1 crosstab
crosstab(text sql) crosstab(text sql, int N)
这crosstab 函数被用来生成
pivot
展示,即通过横向而不是下拉展示(行转列)。例如下面这个例子。原本类型如下:row1 val11 row1 val12 row1 val13 ... row2 val21 row2 val22 row2 val23 ...
但是我们想要的是下面这种格式:
row1 val11 val12 val13 ... row2 val21 val22 val23 ... ...
该
crosstab
函数采用text参数,该参数是生成以第一种方式格式化的原始数据的SQL查询,并生成以第二种方式格式化的表。该sql参数是一个生成源数据集的SQL语句。这个语句必须返回 row_name、category 、value 列。(N这列已经过时,不讨论)
例如:提供的查询可能会产生如下集合:
row_name cat value ----------+-------+------- row1 cat1 val1 row1 cat2 val2 row1 cat3 val3 row1 cat4 val4 row2 cat1 val5 row2 cat2 val6 row2 cat3 val7 row2 cat4 val8
crosstab声明该函数返回setof record,因此必须在FROM在调用SELECT语句的子句中定义输出列的实际名称和类型,例如:
SELECT * FROM crosstab('...') AS ct(row_name text, category_1 text, category_2 text);
这个例子产生一个类似于的集合:
row_name category_1 category_2 ----------+------------+------------ row1 val1 val2 row2 val5 val6
The FROM clause must define the output as one row_name column (of the same data type as the first result column of the SQL query) followed by N value columns (all of the same data type as the third result column of the SQL query)
该crosstab函数为具有相同row_name值的每个连续输入行组生成一个输出行。它value使用value这些行中的字段从左到右填充输出列。如果组中的行少于输出value列,则额外的输出列将填充空值; 如果有更多行,则跳过额外的输入行。
实际上,SQL查询应始终指定ORDER BY 1,2以确保输入行的顺序正确,即具有相同值的值row_name汇集在一起并在行中正确排序。请注意,crosstab它本身并不关注查询结果的第二列; 它只是在那里被命令,以控制第三列值在页面中出现的顺序。
完整案例
CREATE TABLE ct(id SERIAL, rowid TEXT, attribute TEXT, value TEXT); INSERT INTO ct(rowid, attribute, value) VALUES('test1','att1','val1'); INSERT INTO ct(rowid, attribute, value) VALUES('test1','att2','val2'); INSERT INTO ct(rowid, attribute, value) VALUES('test1','att3','val3'); INSERT INTO ct(rowid, attribute, value) VALUES('test1','att4','val4'); INSERT INTO ct(rowid, attribute, value) VALUES('test2','att1','val5'); INSERT INTO ct(rowid, attribute, value) VALUES('test2','att2','val6'); INSERT INTO ct(rowid, attribute, value) VALUES('test2','att3','val7'); INSERT INTO ct(rowid, attribute, value) VALUES('test2','att4','val8'); SELECT * FROM crosstab( 'select rowid, attribute, value from ct where attribute = ''att2'' or attribute = ''att3'' order by 1,2') AS ct(row_name text, category_1 text, category_2 text, category_3 text); row_name | category_1 | category_2 | category_3 ----------+------------+------------+------------ test1 | val2 | val3 | test2 | val6 | val7 | (2 rows)
2.2 CrosstabN(Text)
crosstabN(text sql)
其中N的使用是限制数量
实际案例,N是多少,category就输出多少列:SELECT * FROM crosstab3( 'select rowid, attribute, value from ct where attribute = ''att2'' or attribute = ''att3'' order by 1,2'); row_name | category_1 | category_2 | category_3 ----------+------------+------------+------------ test1 | val2 | val3 | test2 | val6 | val7 | (2 rows)
2.3 crosstab(text, text)
crosstab(text source_sql, text category_sql)
单参数形式的主要限制crosstab是它同样处理组中的所有值,将每个值插入第一个可用列。如果您希望值列对应于特定类别的数据,并且某些组可能没有某些类别的数据,那么这样做效果不佳。crosstab这种情况下的双参数形式通过提供与输出列对应的类别的显式列表来处理
category_sql是一个生成类别集的SQL语句。该语句必须只返回一列。它必须至少生成一行,否则将生成错误。此外,它不能生成重复值,否则将生成错误。category_sql可能是这样的:
案例一:
create table sales(year int, month int, qty int); insert into sales values(2007, 1, 1000); insert into sales values(2007, 2, 1500); insert into sales values(2007, 7, 500); insert into sales values(2007, 11, 1500); insert into sales values(2007, 12, 2000); insert into sales values(2008, 1, 1000); select * from crosstab( 'select year, month, qty from sales order by 1', 'select m from generate_series(1,12) m' ) as ( year int, "Jan" int, "Feb" int, "Mar" int, "Apr" int, "May" int, "Jun" int, "Jul" int, "Aug" int, "Sep" int, "Oct" int, "Nov" int, "Dec" int ); year | Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec ------+------+------+-----+-----+-----+-----+-----+-----+-----+-----+------+------ 2007 | 1000 | 1500 | | | | | 500 | | | | 1500 | 2000 2008 | 1000 | | | | | | | | | | | (2 rows)
案例二:
CREATE TABLE cth(rowid text, rowdt timestamp, attribute text, val text); INSERT INTO cth VALUES('test1','01 March 2003','temperature','42'); INSERT INTO cth VALUES('test1','01 March 2003','test_result','PASS'); INSERT INTO cth VALUES('test1','01 March 2003','volts','2.6987'); INSERT INTO cth VALUES('test2','02 March 2003','temperature','53'); INSERT INTO cth VALUES('test2','02 March 2003','test_result','FAIL'); INSERT INTO cth VALUES('test2','02 March 2003','test_startdate','01 March 2003'); INSERT INTO cth VALUES('test2','02 March 2003','volts','3.1234'); SELECT * FROM crosstab ( 'SELECT rowid, rowdt, attribute, val FROM cth ORDER BY 1', 'SELECT DISTINCT attribute FROM cth ORDER BY 1' ) AS ( rowid text, rowdt timestamp, temperature int4, test_result text, test_startdate timestamp, volts float8 ); rowid | rowdt | temperature | test_result | test_startdate | volts -------+--------------------------+-------------+-------------+--------------------------+-------- test1 | Sat Mar 01 00:00:00 2003 | 42 | PASS | | 2.6987 test2 | Sun Mar 02 00:00:00 2003 | 53 | FAIL | Sat Mar 01 00:00:00 2003 | 3.1234 (2 rows)
您可以创建预定义函数,以避免在每个查询中写出结果列名称和类型。像上面一样,
SELECT DISTINCT attribute FROM cth ORDER BY 1
2.4 Connectby
connectby(text relname, text keyid_fld, text parent_keyid_fld [, text orderby_fld ], text start_with, int max_depth [, text branch_delim ])
该connectby函数生成存储在表中的分层数据的显示。该表必须具有唯一标识行的键字段,以及引用每行的父(如果有)的父键字段。connectby可以显示从任何行下降的子树。
参数描述参数 描述 relname 源关系的名称 keyid_fld 关键字段的名称 parent_keyid_fld 父键字段的名称 orderby_fld 要对同级排序的字段的名称(可选) start_with 要开始的行的键值 max_depth 下降到的最大深度,或无限深度的零 branch_delim 用于在分支输出中分隔键的字符串(可选) 键和父键字段可以是任何数据类型,但它们必须是相同的类型。请注意start_with,无论键字段的类型如何,都必须将该值作为文本字符串输入
connectby声明该函数返回setof record,因此必须FROM在调用SELECT语句的子句中定义输出列的实际名称和类型,例如:
SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'pos', 'row2', 0, '~') AS t(keyid text, parent_keyid text, level int, branch text, pos int);
使用注意事项:
branch_delim
不要出现任何的键值,否者connectby 会报一个无线递归错误,默认提供 ~(It is important that the branch_delim string not appear in any key values, else connectby may incorrectly report an infinite-recursion error. Note that if branch_delim is not provided, a default value of ~ is used for recursion detection purposes.)- 在大表中,除非父节点有索引(In large tables, performance will be poor unless there is an index on the parent-key field.)
- 如果同一父节点的兄弟节点的顺序很重要,请包含orderby_fld参数以指定要按兄弟节点排序的字段。该字段可以是任何可排序的数据类型。输出列列表必须包含最后一个整数序列号列,当且仅当orderby_fld指定时。
CREATE TABLE connectby_tree(keyid text, parent_keyid text, pos int); INSERT INTO connectby_tree VALUES('row1',NULL, 0); INSERT INTO connectby_tree VALUES('row2','row1', 0); INSERT INTO connectby_tree VALUES('row3','row1', 0); INSERT INTO connectby_tree VALUES('row4','row2', 1); INSERT INTO connectby_tree VALUES('row5','row2', 0); INSERT INTO connectby_tree VALUES('row6','row4', 0); INSERT INTO connectby_tree VALUES('row7','row3', 0); INSERT INTO connectby_tree VALUES('row8','row6', 0); INSERT INTO connectby_tree VALUES('row9','row5', 0); -- 带分支,没有orderby_fld(不保证结果的顺序) SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'row2', 0, '~') AS t(keyid text, parent_keyid text, level int, branch text); keyid | parent_keyid | level | branch -------+--------------+-------+--------------------- row2 | | 0 | row2 row4 | row2 | 1 | row2~row4 row6 | row4 | 2 | row2~row4~row6 row8 | row6 | 3 | row2~row4~row6~row8 row5 | row2 | 1 | row2~row5 row9 | row5 | 2 | row2~row5~row9 (6 rows) -- 没有分支,没有orderby_fld(不保证结果的顺序) SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'row2', 0) AS t(keyid text, parent_keyid text, level int); keyid | parent_keyid | level -------+--------------+------- row2 | | 0 row4 | row2 | 1 row6 | row4 | 2 row8 | row6 | 3 row5 | row2 | 1 row9 | row5 | 2 (6 rows) -- 带分支,有排序。(notice that row5 comes before row4) SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'pos', 'row2', 0, '~') AS t(keyid text, parent_keyid text, level int, branch text, pos int); keyid | parent_keyid | level | branch | pos -------+--------------+-------+---------------------+----- row2 | | 0 | row2 | 1 row5 | row2 | 1 | row2~row5 | 2 row9 | row5 | 2 | row2~row5~row9 | 3 row4 | row2 | 1 | row2~row4 | 4 row6 | row4 | 2 | row2~row4~row6 | 5 row8 | row6 | 3 | row2~row4~row6~row8 | 6 (6 rows) -- without branch, with orderby_fld (notice that row5 comes before row4) SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'pos', 'row2', 0) AS t(keyid text, parent_keyid text, level int, pos int); keyid | parent_keyid | level | pos -------+--------------+-------+----- row2 | | 0 | 1 row5 | row2 | 1 | 2 row9 | row5 | 2 | 3 row4 | row2 | 1 | 4 row6 | row4 | 2 | 5 row8 | row6 | 3 | 6 (6 rows)
参考
-
Sybase15实现 自定义行转列函数
2016-05-04 10:43:30Sybase15实现 自定义行转列函数Sybase15支持自定义函数,很多时候B/S开发需要使用行转列函数,下面我举一例供大家参考!根据表的id实现获取表所有字段的函数,id,可以根据表名通过sysobjects系统表获取到。
create function fn_columnnames (@tblid int) returns varchar(1000) as begin declare @vcolname varchar(50),@vcolnames varchar(1000) select @vcolnames='' declare cur_column cursor for select name from syscolumns where id=@tblid open cur_column fetch next from cur_column into @vcolname while (@@fetch_status = 0) begin select @vcolnames=@vcolnames+@vcolname+',' fetch next from cur_column into @vcolname end close cur_column deallocate cur_column if len(@vcolnames) > 0 select @vcolnames = substring(@vcolnames,1,len(@vcolnames) -1) -- return the result of the function return @vcolnames end
-
MYSQL一个字段多个值拼接成一列函数group_concat
2019-09-24 17:12:01MYSQL一个字段多个值拼接成一列函数group_concat示例函数相关配置 示例 1.单列多行合并单行单列展示 select group_concat(name) as name from student where classId = 1 执行结果如下图: 2.多列拼接,多列多行值...MYSQL一个字段多个值拼接成一列函数group_concat
示例
1.单列多行合并单行单列展示
select group_concat(name) as name from student where classId = 1
执行结果如下图:
2.多列拼接,多列多行值合并成单行单列显示
select group_concat(name,sex) as name from student where classId = 1
执行结果如下图:
3. 值排序拼接select group_concat(name order by id desc) as name from student where classId = 1
函数相关配置
1.group_concat长度:
group_concat默认最大长度是1024,超过截取前1024长度字符。
2.查询group_concat长度SQL
show variables like 'group_concat_max_len';
3.更改group_concat长度配置
①修改MYSQL的配置文件my.ini(需要重启mysql服务):
group_concat_max_len = 2048;
②执行语句
SET GLOBAL group_concat_max_len=1024000; SET SESSION group_concat_max_len=1024000;
扫二维码关注博主公众号👇
-
Oracle 行转列函数
2019-05-29 15:19:29pivot (sum(amount) for type in( '001' 截止上月设计申请金额 , '002' 截止上月现场申请金额 )); pivot(聚合函数 for 转列后列的字段 in (列字段值 别名)); -
Oracle:行转列函数,查询重复记录函数,过滤表中重复记录函数
2012-11-08 09:38:361 行转列函数: select wm_concat(username) from users 2 查询重复记录函数 select t.* from(select t.*,rownum rn from USERS t where t.username in (select username from USERS group by username having... -
c++中队列函数queue的常用
2018-10-22 22:55:52即可使用队列类,其中在面试或笔试中常用的成员函数如下(按照最常用到不常用的顺序) push() pop() size() empty() front() back() 接下来逐一举例说明: push() 队列中由于是先进先出,push即在... -
oracle中的行转列函数
2018-07-24 14:44:44最近项目需要进行行转列,经过上网查资料发现了wmsys.wm_concat和LISTAGG函数,在这分享给大家 wmsys.wm_concat是oracle 10g推出的,用来连接字符串,LISTAGG是oracle 11g推出的,它的作用和wmsys.w... -
MySQL行转列函数
2019-11-04 09:07:51原文链接: ...概述 好久没写SQL语句,今天看到问答中的一个问题,拿来...学校里面记录成绩,每个人的选课不一样,而且以后会添加课程,所以不需要把所有课程当作列。数据表里面数据如下图,使用姓名+课程作为联合主键(... -
spark dataFrame 新增一列函数withColumn
2017-05-30 11:34:02往一个dataframe新增某个列是很常见的事情。 ...然而这个资料还是不多,很多都需要很多变换。...不过由于这回需要增加的列非常简单,倒也没有必要再用UDF函数去修改列。 利用withColumn函数就能实现对da -
hive的虚拟列函数
2016-02-14 15:46:37hive的虚拟列 hive 0.8版本之后提供了两个函数,其实也没什么用处,可以起到辅助函数的作用。 INPUT__FILE__NAME:函数的作用就是显示每一个map任务所对应输文件的目录以及文件名。 BLOCK__OFFSET__INSIDE__... -
oracle行转列函数
2016-12-05 18:03:39WMSYS.WM_CONCAT 函数的用法 select t.rank, t.Name from t_menu_item t; 10 CLARK 10 KING 10 MILLER 20 ADAMS 20 FORD 20 JONES 20 SCOTT 20 SMITH 30 ALLEN -
Sql Server 行转列函数- Pivot 的使用教程
2018-09-05 15:04:19PIVOT 提供的语法比一系列复杂的...非透视的列>, [第一个透视的列] AS <列名称>, [第二个透视的列] AS <列名称>, ... [最后一个透视的列] AS <列名称>, FROM ... -
Oracle 关于oracle自带的行转列函数
2016-03-11 14:55:40目标是将某表中根据id分组后将name字段的值拼接到一列中,且用“,“进行分割 试过用 wm_concat() 结合 group by , 在pl/sql中执行正常, 但是在java程序运行时会报: ORA-22922:不存在的 LOB 值 1.... -
python 中pd.pivot_table行转列函数使用
2018-10-25 16:30:46 -
PostgreSQL的行转列函数使用一例
2014-11-04 18:09:27开发同事说使用postgres的扩展行转列应用时有一个问题,示例如下 该扩展包初步使用参考: http://my.oschina.net/Kenyon/blog/54357 一、环境 OS:CentOS 6.3 DB:PostgreSQL 9.3.0 二、场景 ... -
Oracle行转列函数WMSYS.WM_CONCAT() 和 Listagg()
2014-05-06 13:39:49WMSYS.WM_CONCAT: 依赖WMSYS 用户,不同oracle环境时可能...LISTAGG : 11g2才提供的函数,不支持distinct,拼接长度不能大于4000,函数返回为varchar2类型,最大长度为4000. WMSYS.WM_CONCAT() select deptno, -
sql server 2005或2008实现oracle行转列函数wm_concat
2013-05-23 16:38:37但在sql server里没有这个函数,那怎么办?只能另找其他方法了。 select id, [ names ] = stuff (( select ' , ' + [name ] from table1 A t where A. id =B. id for xml path( '' )... -
Hive常用函数之行转列和列转行函数
2020-02-24 20:47:16一、行转列函数:CONCAT,CONCAT_WS,COLLECT_SET 在HIVE实际开发过程中,我们会遇到“行转列”和“列转行”的场景。比如: 1、需要取“订单号”对应的所有商品“SKU号”,商品“sku号”放在一列,即从table1查询... -
oracle 行转列 列转行函数理解
2019-12-10 18:14:421、pivot 行转列函数 含义:pivot 聚合函数(字段1) for 字段2 in (值1,值2,值3...) 将字段2中的值1,值2,值3...作为字段名称在对字段1根据聚合函数做聚合(SUM、max、min、avg等)。 例、原表为: 将这个表格...