正文 

当前位置： 首页 > 程序 > java教程

行转列(代码片段)

张侦毅 张侦毅     2022-12-09     791

关键词：

文章目录

使用场景

  有时我们在进行图表数据统计时，在图表中，横轴为月份（英文大写），纵轴为图表数据。但是我们所使用的数据源表结构如下面的数据源所示，而此时，下面的一条数据，其实就需要我们将其行转列，进行格式化，然后将数据显示在图表中。

  当然，我们可以通过存储过程方法解决该问题，但是此处我并不推荐使用存储过程，因为由于业务的复杂性，我们在其它地方也可能会用到行转列后的数据的连表查询操作，而如果我们使用存储过程，这就意味着我们并不能再使用连表查询操作来处理存储过程了，而如果要在其它地方，仍然能够对行转列后的数据进行连表查询操作，这里我推荐使用视图方法。

数据源

  建表语句如下：

CREATE TABLE `dept_data` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `name` varchar(64) DEFAULT NULL COMMENT '部门',
  `year` int(10) DEFAULT NULL COMMENT '年份',
  `month_1` double DEFAULT NULL COMMENT '一月数据',
  `month_2` double DEFAULT NULL COMMENT '二月数据',
  `month_3` double DEFAULT NULL COMMENT '三月数据',
  `month_4` double DEFAULT NULL COMMENT '四月数据',
  `month_5` double DEFAULT NULL COMMENT '五月数据',
  `month_6` double DEFAULT NULL COMMENT '六月数据',
  `month_7` double DEFAULT NULL COMMENT '七月数据',
  `month_8` double DEFAULT NULL COMMENT '八月数据',
  `month_9` double DEFAULT NULL COMMENT '九月数据',
  `month_10` double DEFAULT NULL COMMENT '十月数据',
  `month_11` double DEFAULT NULL COMMENT '十一月数据',
  `month_12` double DEFAULT NULL COMMENT '十二月数据',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8

  在表中插入数据：

insert  into `dept_data`(`id`,`name`,`year`,`month_1`,`month_2`,`month_3`,`month_4`,`month_5`,`month_6`,`month_7`,`month_8`,`month_9`,`month_10`,`month_11`,`month_12`) values (1,'部门1',2017,1,2,3,4,5,6,7,8,9,10,11,12),(2,'部门2',2018,1.1,2.2,3.3,4.4,5.5,6.6,7.7,8.8,9.9,10.1,11.11,12.12);

行转列

创建个月视图

  创建一月份视图：

CREATE VIEW dept_data_month_01_view AS 
SELECT 
  dd.`id`,
  1 AS month_index,
  dd.`name`,
  STR_TO_DATE(CONCAT(dd.`year`, '-01'), '%Y-%m') AS DATE,
  dd.year,
  dd.`month_1` AS DATA 
FROM
  dept_data dd;

   创建二月份视图

CREATE VIEW dept_data_month_02_view AS 
SELECT 
  dd.`id`,
  2 AS month_index,
  dd.`name`,
  STR_TO_DATE(CONCAT(dd.`year`, '-02'), '%Y-%m') AS DATE,
  dd.year,
  dd.`month_2` AS DATA 
FROM
  dept_data dd;

   以此类推，创建各月视图，在这里，我就不一一展示其创建视图语句了。

将个月视图拼接成完整月度数据视图

   当我们将所有月份的视图创建完成后，再将各月的视图拼接成一个完整的月度数据视图，其完整月度数据视图创建语句如下：

CREATE VIEW dept_data_month_view AS
SELECT * FROM dept_data_month_01_view
UNION ALL
SELECT * FROM dept_data_month_02_view
UNION ALL
SELECT * FROM dept_data_month_03_view
UNION ALL
SELECT * FROM dept_data_month_04_view
UNION ALL
SELECT * FROM dept_data_month_05_view
UNION ALL
SELECT * FROM dept_data_month_06_view
UNION ALL
SELECT * FROM dept_data_month_07_view
UNION ALL
SELECT * FROM dept_data_month_08_view
UNION ALL
SELECT * FROM dept_data_month_09_view
UNION ALL
SELECT * FROM dept_data_month_10_view
UNION ALL
SELECT * FROM dept_data_month_11_view
UNION ALL
SELECT * FROM dept_data_month_12_view

将完整月度数据视图进行格式化

   由于我们最终是要将其展示在图表中的，而在图表中，横轴是大写的英文月份，因而我们又需要将行转列后的数据执行格式化操作。

CREATE VIEW dept_data_month_format_view AS
SELECT 
ddmv.`id`,
ddmv.`month_index`,
ddmv.`name`,
ddmv.`year`,
ddmv.`DATE`,
CASE
    WHEN DATE_FORMAT(ddmv.`date`, '%m') = 01 
    THEN 'Jan' 
    WHEN DATE_FORMAT(ddmv.`date`, '%m') = 02 
    THEN 'Feb' 
    WHEN DATE_FORMAT(ddmv.`date`, '%m') = 03 
    THEN 'Mar' 
    WHEN DATE_FORMAT(ddmv.`date`, '%m') = 04 
    THEN 'Apr' 
    WHEN DATE_FORMAT(ddmv.`date`, '%m') = 05 
    THEN 'May' 
    WHEN DATE_FORMAT(ddmv.`date`, '%m') = 06 
    THEN 'Jun' 
    WHEN DATE_FORMAT(ddmv.`date`, '%m') = 07 
    THEN 'Jul' 
    WHEN DATE_FORMAT(ddmv.`date`, '%m') = 08 
    THEN 'Aug' 
    WHEN DATE_FORMAT(ddmv.`date`, '%m') = 09 
    THEN 'Sep' 
    WHEN DATE_FORMAT(ddmv.`date`, '%m') = 10 
    THEN 'Oct' 
    WHEN DATE_FORMAT(ddmv.`date`, '%m') = 11 
    THEN 'Nov' 
    WHEN DATE_FORMAT(ddmv.`date`, '%m') = 12 
    THEN 'Dec' 
  END month_format,
ddmv.`DATA`
FROM dept_data_month_view ddmv ORDER BY ddmv.`month_index` ASC

图表展示效果

   最终在图表中的展示效果（2018年）：

计算月度平均值

创建月度数据平均值视图

   而如果我们要统计每个月的平均值，如下：

月份平均值
一月1月份数据除以1
二月1、2月份数据之和除以2
三月1、2、3月份数据之和除以3
……

   此时，月度数据平均值视图创建语句如下：

CREATE VIEW dept_data_month_format_target_view AS 
SELECT 
  ddmfv.`id`,
  ddmfv.`month_index`,
  ddmfv.`name`,
  ddmfv.`year`,
  ddmfv.`DATE`,
  ddmfv.`month_format`,
  (SELECT 
    SUM(ddmfv2.DATA) 
  FROM
    dept_data_month_format_view ddmfv2 
  WHERE ddmfv2.DATE <= ddmfv.`DATE` 
    AND ddmfv2.name = ddmfv.`name`) / ddmfv.`month_index` month_average 
FROM
  dept_data_month_format_view ddmfv 
ORDER BY ddmfv.month_index ASC

图表展示效果

  结合图表一中的数据，其最终的展示结果如下：

  当我们将鼠标指针指上去时，我们可以看到当前月份所展示的数据值：

oracle行转列(代码片段)

行转列，例子：统计各科各出院情况人数withtas( SELECTb.科室ID,a.患者ID,a.resultFROM患者信息表awherea.出院时间>=to_date(\'2021-6-100:00:00\',\'yyyy-MM-ddhh24:mi:ss\')anda.出院时间<=to_date(\'2021-6-2023:59:59\',\'yyyy-MM-ddhh24:mi:s 查看详情

行转列(代码片段)

文章目录使用场景数据源行转列创建个月视图将个月视图拼接成完整月度数据视图将完整月度数据视图进行格式化图表展示效果计算月度平均值创建月度数据平均值视图图表展示效果使用场景  有时我们在进行图表数据统计时... 查看详情

hivesql行列转换(行转列，列转行)(代码片段)

在Hivesql应用中会遇到“行转列”和“列转行”的场景，下面介绍其基本使用语法。1.行转列：关键字：collect_set()/collect_list()、concat_ws()1）collect_set()/collect_list()：collect_set()函数只接受基本数据类型，作用是... 查看详情

sql面试题：行转列(代码片段)

...LSELECT‘C‘,159,400,20,320UNIONALLSELECT‘D‘,250,30,15,15  行转列后：SELECTP.ProgrectName,P.Supplier,P.SupplyNumFROM(SELECTProgrectName,OverseaSupply,NativeSupply,SouthSupply,NorthSupplyFROMProgrectDetail)TUNPIVOT(SupplyNumFORSupplierIN(OverseaSupply,NativeSupply,SouthSupply,Nor... 查看详情

oracle逗号分割，列转行，行转列转载(代码片段)

https://www.cnblogs.com/gdzhong/p/4726315.html?tvd SQL代码列转行selectREGEXP_SUBSTR(a.rolecode,'[^,]+',1,l)rolecodefrom(select'a,aa,aaa'rolecodefromdual)a,(SELECTLEVELlFROMDUALCONNECTBY 查看详情

hive行转列lateralviewexplode使用方法(代码片段)

原数据表a，每列都是由逗号分隔的字符串：bb_id1,2,31,12,3我们需要的是bb_id1231123使用LATERALVIEWexplode函数可进行行转列selectbb_id2fromaLATERALVIEWexplode(split(bb_id),','))bbidasbb_id2;  查看详情

sqlserver的forxmlpath与变通的行转列(代码片段)

...以通过这个语法做一些变通实现一些特定的功能，比如说行转列。要会变通的话，当然首先是要知道FORXMLPATH的语法。FORXMLPATH的简单语法假设有一个hobby表（爱好），表中有两个字段，一个是hobbyID（爱好id），一个是hName（爱好名... 查看详情

hive函数之~行转列与列转行(代码片段)

4、行转列1．相关函数说明CONCAT(stringA/col,stringB/col…)：返回输入字符串连接后的结果，支持任意个输入字符串;CONCAT_WS(separator,str1,str2,...)：它是一个特殊形式的CONCAT()。第一个参数剩余参数间的分隔符。分隔符可以是与剩余参... 查看详情

行转列，列转行，图一转图二或图二转图一(代码片段)

图一：NamCourseScorezhangsanChinese85zhangsanMaths76zhangsanEnglish80lisiChinese82lisiMaths90lisiEnglish55图二：NamChineseMathsEnglishzhangsan857680lisi829055图一转图二：SELECTNam,SUM(IF(Course=‘Chinese‘,Score,0) 查看详情

oracle行转列方法集合(代码片段)

...09;语法：select需要分组的字段,wmsys.wm_concat(distinct需要行转列合并展示的字段)from表名groupby需要分组的字段;实战：查询各年级涉及的班级，多个班级默认用“,”隔开按一行合并展示。select*fromCLASS_STATISTICS; selectGRADE,wms... 查看详情

hive面试题系列第二题-行转列问题(代码片段)

视频讲解地址：https://www.bilibili.com/video/BV1BG4y1v7Ps/?spm_id_from=333.788&vd_source=aa4fb0436f6d978af872cafb81a01178Hive面试题系列第二题-行转列问题题目：求语文课程成绩大于英语课程成绩的学生的学号表结构:createtablesco 查看详情

行转列(代码片段)

...group_cancat(course,”:”,”score”)  不过行转列用decode()也行  DECODE与MAX的联合使用SELECTDISTINCTEMP_NO,MAX(DECODE(TO_CHAR(WORK_DATE,‘DD‘),‘01‘,CARD_FREQUENCY))D01,MAX(DECODE(TO_CHAR(WORK_DATE,‘DD‘),‘02‘,CARD_FREQUENCY))D02,MAX(... 查看详情

hive_行转列(多行转一个集合/数组)(代码片段)

1．相关函数说明CONCAT(stringA/col,stringB/col…)：　　返回输入字符串连接后的结果，支持任意个输入字符串;CONCAT_WS(separator,str1,str2,...)：　　它是一个特殊形式的CONCAT()。　　第一个参数剩余参数间的分隔符。　　分隔符可以是... 查看详情

⭐️leetcode解题系列⭐️1179.重新格式化部门表（oraclepivot行转列函数）(代码片段)

...️Pivot函数简介创建测试表Department插入测试数据执行Pivot行转列函数❄️写在最后❤️原题❤️部门表Department：+---------------+---------+|ColumnName|Type|+---------------+---------+|id 查看详情

⭐️leetcode解题系列⭐️1179.重新格式化部门表（oraclepivot行转列函数）(代码片段)

...️Pivot函数简介创建测试表Department插入测试数据执行Pivot行转列函数❄️写在最后❤️原题❤️部门表Department：+---------------+---------+|ColumnName|Type|+---------------+---------+|id 查看详情

大数据（8n）图解spark行转列pivot数据透视表(代码片段)

文章目录1、透视1.1、SQL1.2、Spark2、逆透视2.1、SQL2.2、Spark3、Appendix1、透视1.1、SQLSELECT*FROMtPIVOT(SUM(d)FORcIN('c1'asc1,'c2'asc2,'c3'asc3));1.2、Sparkimportorg.apache.spark.sql.SparkSessionim 查看详情

hive之列转行,行转列(代码片段)

...t不去重，collect_set去重。column的数据类型要求是string 行转列测试数据hive>select*fromlie_col;OKlie_col.user_idlie_col.order_value1044082909888,2662805,292 查看详情

sql面试：sql中的行转列和列转行(代码片段)

...技能。而在SQL面试中，一道出镜频率很高的题目就是行转列和列转行的问题，可以说这也是一道经典的SQL题目，本文就这一问题做以介绍分享。给定如下模拟数据集，这也是SQL领域经典的学生成绩表问题。两张 查看详情