精华内容
下载资源
问答
  • sql unpivot 我最近在一个匿名用户的Stack Overflow上遇到一个非常有趣的问题 。 问题是关于使用表值函数在Oracle中生成以下形式的表: Description COUNT ------------------- TEST1 10 TEST2 15 TEST3 25 TEST4...

    sql unpivot

    我最近在一个匿名用户的Stack Overflow上遇到一个非常有趣的问题 。 问题是关于使用表值函数在Oracle中生成以下形式的表:

    Description   COUNT
    -------------------
    TEST1         10 
    TEST2         15
    TEST3         25
    TEST4         50

    应该为COUNT列实现的逻辑如下:

    • TEST1:工资<10000的员工人数
    • TEST2:部门> 10的员工数
    • TEST3:雇用日期>(SYSDATE-60)的员工数
    • TEST4:等级= 1的员工人数

    接受挑战!

    对于本练习,我们假设下表:

    CREATE TABLE employees (
      id NUMBER(18)     NOT NULL PRIMARY KEY,
      sal NUMBER(18, 2) NOT NULL,
      dept NUMBER(18)   NOT NULL,
      hiredate DATE     NOT NULL,
      grade NUMBER(18)  NOT NULL
    );
    
    INSERT INTO employees 
          VALUES (1, 10000,  1, SYSDATE     , 1);
    INSERT INTO employees 
          VALUES (2,  9000,  5, SYSDATE - 10, 1);
    INSERT INTO employees 
          VALUES (3, 11000, 13, SYSDATE - 30, 2);
    INSERT INTO employees 
          VALUES (4, 10000, 12, SYSDATE - 80, 2);
    INSERT INTO employees 
          VALUES (5,  8000,  7, SYSDATE - 90, 1);

    如何计算COUNT个值

    第一步,我们将研究如何最好地计算COUNT个值。 最简单的方法是在单独的列而不是行中计算值。 SQL新手可能会使用嵌套的SELECT求助于规范的解决方案,由于性能原因,这是非常糟糕的:

    SELECT
      (SELECT COUNT(*) FROM employees 
          WHERE sal < 10000) AS test1,
      (SELECT COUNT(*) FROM employees 
          WHERE dept > 10) AS test2,
      (SELECT COUNT(*) FROM employees 
          WHERE hiredate > (SYSDATE - 60)) AS test3,
      (SELECT COUNT(*) FROM employees 
          WHERE grade = 1) AS test4
    FROM dual;

    为什么查询不是最佳的? 有四个表访问可查找所有数据:

    取消嵌套选择

    如果将索引添加到要过滤的每个单独的列中,则至少有可能优化单个子查询,但是对于此类报告,偶尔进行全表扫描非常好,尤其是当您聚合大量数据时。

    即使速度不是最佳的,上面的方法也会产生正确的结果:

    TEST1   TEST2   TEST3   TEST4
    -----------------------------
    2	2	3	3

    那么如何改善查询呢?

    很少有人意识到聚合函数仅聚合非NULL值。 当您编写COUNT(*) ,这没有任何作用,但是当您将一个表达式传递给COUNT(expr)函数时,这将变得更加有趣!

    这里的想法是,您使用CASE表达式将每个谓词的TRUE评估转换为非NULL值,将FALSE (或NULL )评估转换为NULL 。 以下查询说明了这种方法

    SELECT
      COUNT(CASE WHEN sal < 10000 THEN 1 END)
          AS test1,
      COUNT(CASE WHEN dept > 10 THEN 1 END)
          AS test2,
      COUNT(CASE WHEN hiredate > (SYSDATE-60) THEN 1 END)
          AS test3,
      COUNT(CASE WHEN grade = 1 THEN 1 END)
          AS test4
    FROM employees;

    …并再次产生正确的结果:

    TEST1   TEST2   TEST3   TEST4
    -----------------------------
    2	2	3	3

    使用FILTER()代替CASE

    SQL标准和强大的PostgreSQL数据库为上述功能提供了更加方便的语法。 关于聚合函数鲜为人知的FILTER()子句

    在PostgreSQL中,您将改为编写:

    SELECT
      COUNT(*) FILTER (WHERE sal < 10000)
          AS test1,
      COUNT(*) FILTER (WHERE dept > 10)
          AS test2,
      COUNT(*) FILTER (WHERE hiredate > (SYSDATE - 60))
          AS test3,
      COUNT(*) FILTER (WHERE grade = 1)
          AS test4
    FROM employees;

    当您要将FILTER()条件与要用于聚合的任何其他表达式完全分开时,这很有用。 例如,当计算SUM()

    无论如何,查询现在只需要在表中命中一次。 然后可以完全在内存中执行聚合。

    关键案例表达

    除非您为每个聚合都有一个索引, 否则这总是比以前的方法更好。

    现在如何获取行中的结果?

    关于堆栈溢出的问题想要将TESTn值放入单独的行而不是列中的结果。

    Description   COUNT
    -------------------
    TEST1         2
    TEST2         2
    TEST3         3
    TEST4         3

    再次,有一种规范的,不太好的方法可以使用UNION ALL来做到这一点:

    SELECT 
      'TEST1' AS Description, 
      COUNT(*) AS COUNT 
    FROM employees WHERE sal < 10000
    UNION ALL
    SELECT 
      'TEST2', 
      COUNT(*)
    FROM employees WHERE dept > 10
    UNION ALL
    SELECT 
      'TEST3', 
      COUNT(*) 
    FROM employees WHERE hiredate > (SYSDATE - 60)
    UNION ALL
    SELECT 
      'TEST4', 
      COUNT(*) 
    FROM employees WHERE grade = 1

    除了列/行换位(“ unpivoting”)外,此方法或多或少与嵌套选择方法等效。 和计划也非常相似:

    全部取消联合

    换位=(不)透视

    注意我如何使用术语“移调”。 这就是我们所做的,并且它的名字是:(un)pivoting。 它不仅具有名称,而且还可以通过可在表引用之后放置的PIVOTUNPIVOT关键字在Oracle和SQL Server中立即使用此功能。

    • PIVOT将行转置为列
    • UNPIVOT将列转置回行

    因此,我们将采用原始的最佳解决方案,并将其与UNPIVOT换位

    SELECT *
    FROM (
      SELECT
        COUNT(CASE WHEN sal < 10000 THEN 1 END)
          AS test1,
        COUNT(CASE WHEN dept > 10 THEN 1 END)
          AS test2,
        COUNT(CASE WHEN hiredate > (SYSDATE-60) THEN 1 END)
          AS test3,
        COUNT(CASE WHEN grade = 1 THEN 1 END)
          AS test4
      FROM employees
    ) t
    UNPIVOT (
      count FOR description IN (
        "TEST1", "TEST2", "TEST3", "TEST4"
      )
    )

    我们需要做的就是将原始查询包装在派生表t (即FROM子句中的内联SELECT ),然后对该表t进行“ UNPIVOT ”处理,生成countdescription列。 结果再次是:

    Description   COUNT
    -------------------
    TEST1         2
    TEST2         2
    TEST3         3
    TEST4         3

    执行计划仍然是最佳的。 所有动作都在内存中发生。

    不可撤销

    结论

    PIVOTUNPIVOT是用于报告和重组数据的非常有用的工具。 像上面一样有很多用例,您想在其中重新组织一些聚合。 其他用例包括实现实体属性值模型的设置或属性表,并且您想要将属性从行转换为列( PIVOT ),或从列转换为行( UNPIVOT

    感兴趣吗? 在这里阅读有关PIVOT信息:

    翻译自: https://www.javacodegeeks.com/2016/01/impress-coworkers-using-sql-unpivot.html

    sql unpivot

    展开全文
  • SQL unPivot 列转行操作

    2019-02-22 15:25:45
    效果 : 原表: SELECT * FROM pvt 列转行: SELECT * FROM pvt p UNPIVOT( Orders FOR Employee IN (Emp1, Emp2, Emp3, Emp4, Emp5) )AS unpvt;

    效果 :

    原表:  SELECT * FROM pvt

    列转行:

    SELECT * FROM pvt  p  UNPIVOT(
    
    	Orders FOR Employee IN  (Emp1, Emp2, Emp3, Emp4, Emp5)
    
    )AS unpvt;

    展开全文
  • sqlserver使用UNPIVOT函数列转行
  • SQLUNPIVOT是什么

    2020-12-17 02:25:16
    比如你有一个table,你想把列转换成行,就可以使用unpivot。还是有点抽象,具体什么意思呢? 比如说有一个table stu_score如下: Name, Math, Chinese zhc, 99, 95 xiaoy, 89, 100 xiaot, 98, 60 你想要把它...

    比如你有一个table,你想把列转换成行,就可以使用unpivot。还是有点抽象,具体什么意思呢?

    比如说有一个table stu_score如下:

    Name, Math, Chinese
    
    zhc, 99, 95
    
    xiaoy, 89, 100
    
    xiaot, 98, 60

    你想要把它转化成这样:

    Name, Subject, Score
    
    zhc, Math, 99
    
    zhc, Chinese, 95
    
    xiaoy, Math, 89
    
    xiaoy, Chinese, 100
    
    xiaot, Math, 98
    
    xiaot, Chinese, 60

    就是把一行转换成多行,可以这样写SQL用unpivot:

    select u.name, u.subject, u.score
    from stu_score
    unpivot
    (
      score
      for subject in (Math, Chinese)
    ) u;

    另外一种方法还可以使用union来进行一行到多行的转换,相对来说比unpivot容易理解。 好了,你懂了吗?

     

    原文:http://blog.csdn.net/hongchangfirst/article/details/111306968

    作者:hongchangfirst

    hongchangfirst的主页:http://blog.csdn.net/hongchangfirst

     

    展开全文
  • SQL UNPIVOT详解

    2013-11-21 14:36:00
    一、UNPIVOT:  UNPIVOT与PIVOT正好相反,它把数据从列旋转到行 例:  首先将源数据显示出来,看一下:    我们先把empid为1和2的2006年数据修改为null UPDATE dbo.EmpYearValues SET [2006] = NULL ...

    一、UNPIVOT:

      UNPIVOT与PIVOT正好相反,它把数据从列旋转到行

    例:

      首先将源数据显示出来,看一下:

      

      我们先把empid为1和2的2006年数据修改为null

    UPDATE dbo.EmpYearValues
      SET [2006] = NULL
    WHERE empid IN(1, 2);

      设置完成之后在查询源数据:如下:

      

      下面我们进行UNPIVOT操作:

    SELECT empid, orderyear, val
    FROM dbo.EmpYearValues
      UNPIVOT(val FOR orderyear IN([2006],[2007],[2008])) AS U;

      下面显示查询结果集:

      

     

    二、UNPIVOT操作涉及的逻辑处理阶段

      UNPIVOT涉及到以下三个逻辑阶段:

      1、生成副本

      2、提取元素

      3、删除带有NULL的行

      UNPIVOT的输入是左表表达式,第一步先为左表表达式中的行生成多个副本,要进行逆透视转换的每一列都会生成一个副本。因为这里的in子句中有三个列名称,所以要为每个来源行生成三个副本。结果得到的虚拟表将包含一个新列,用于以字符串格式保存来源列的名称。该列的名称是紧接在IN子句之前指定的名称(在这里也就是orderyear)。虚拟表部分(只显示empid为3、6、9的数据)如下:

      

      第二步从来源列中提取出于行的当前副本说代表的逆透视转换元素相对应的值。保存该值的目标列名称是紧接在for子句之前指定的(在这个例子中是val)。这个目标列将保存虚拟表中与当前行的订单年份相对应的列值。查询的虚拟表如下:

      

      第三部,也是最后一步将删除掉结果值列为null的行。

    转载于:https://www.cnblogs.com/sundebin68/p/3435534.html

    展开全文
  • 文章目录pivot行转列例子1例子2例子3unpivot列转行 pivot行转列 pivot 用于将列值旋转为列名(即行转列),在SQLServer 2000可以用聚合函数配合CASE语句实现 即SQL语句行列转换的两种方法 case…when与pivot函数的...
  • SQL之pivot和unpivot的用法,pivot用于行转列,unpivot用于列转行
  • SQL使用 PIVOT 和 UNPIVOT

    千次阅读 2019-04-16 15:53:36
    可以使用 PIVOT 和 UNPIVOT 关系运算符将表值表达式更改为另一个表。PIVOT 通过将表达式某一列中的唯一值转换为输出中的多个列来旋转表值表达式,并在... 对升级到 SQL Server 2005 或更高版本的数据库使用 PIVOT ...
  • 它不仅具有名称,而且还可以通过可在表引用之后放置的PIVOT和UNPIVOT关键字在Oracle和SQL Server中立即使用此功能。 PIVOT将行转置为列 UNPIVOT将列转置回行 因此,我们将采用原始的最佳解决方案,并将其与UNPIVOT...
  • SQL 之列转行Unpivot函数

    千次阅读 2019-08-02 15:10:08
    上次我们谈到行转列,用的是Pivot函数,这次我们来谈谈Unpivot函数。(这里是用的数据库是SQLSERVER,与其他数据库是类似的,大家放心看就好) 先看一个小问题 在这张图中,表示的是顾客用不同手机号给Phone1、...
  • 上篇博客介绍了静态方式的行列转换,这次介绍一下利用SQL SERVER 函数实现的行列转换,行转列测试数据同之前的一样:--测试数据 if not object_id(N'Tempdb..#T') is null drop table #T Go Create table #T(...
  • UNPIVOT与PIVOT执行相反的操作,将表值表达式的列转换为列值。   通俗简单的说:PIVOT就是行转列,UNPIVOT就是列转行 一、PIVOT实例 1. 建表 建立一个销售情况表,其中,year字段表示年份,quarter字段...
  • 一、使用PIVOT和UNPIVOT命令的SQL Server版本要求 1.数据库的最低版本要求为SQL Server 2005 或更高。 2.必须将数据库的兼容级别设置为90 或更高。 3.查看我的数据库版本及兼容级别。 如果不知道怎么看数据库...
  • SQL UNPIVOT和PIVOT

    2015-12-25 14:56:00
    /* table_source PIVOT( 聚合函数(value_column) FOR pivot_column IN(<column_list>) ) ...UNPIVOT( value_column FOR pivot_column IN(<column_list&...
  • 行列之间的互相转换是ETL中的常见需求,在Spark SQL中,行转列有内建的PIVOT函数可用,没什么特别之处。而列转行要稍微麻烦点。本文整理了2种可行的列转行方法,供参考。 1 测试数据准备 本文的环境是Windows 10, ...
  • 一、使用PIVOT和UNPIVOT命令的SQL Server版本要求 1.数据库的最低版本要求为SQL Server 2005 或更高。 2.必须将数据库的兼容级别设置为90 或更高。 3.查看我的数据库版本及兼容级别。 如果不知道怎么看...
  • 主要给大家介绍了关于SQL知识点之列转行Unpivot函数的相关资料,文中通过示例代码介绍的非常详细,对大家学习或者使用SQL具有一定的参考学习价值,需要的朋友们下面来一起学习学习吧
  • from ##table UNPIVOT(qty for star in (S1, S2,S3))     Colum1 S1 S2 S3 1 3 5 6 2 5 6 5 3 2 3 7 ...
  • SQLServer列转行函数Unpivot会将多列转化为多行,列名转换为列值,会新增两个column:一个column用于存储列名,一个column用于存储列值。
  • select * from dbo.CustomerPhones 查看数据如下: Upivot 实现行转列 select * from dbo.CustomerPhones -----数据源 unpivot ( Phone FOR Phones IN (Phone1, Phone2, Phone3) ##Phone1、Phone2、Phone3这些列的...
  • https://www.cnblogs.com/caoshiqing/p/6972631.html https://www.cnblogs.com/mangonic/archive/2012/05/17/2506980.html https://bbs.pinggu.org/forum.php?mod=viewthread&tid=6407887
  • SQLserver UNPIVOT函数 行列转化出现 [Err] 42000 - [SQL Server]关键字 'FOR' 附近有语法错误。解决办法 编辑数据库将兼容级别改为90
  • 一、这次讲啥 pivot的用法之前有写过了,然后前几天呢,某群友提了...pivot和unpivot的用法我就不重复讲了,可以看下面的测试代码理解一下。 二、sql 1、测试数据准备 -- 建个表测试 create table orgCost( or...
  • 同学想要的效果,第一个需要将列标题beta_l_n,beta_r_n,beta_l_e,beta_r_e,beta_l_w,beta_r_w,beta_l_s,beta_r_s转换为行值,所以需要unpivot 操作 第二需要将time_during对应的行值属性进行行转列操...
  • 存在如下一张临时表: UNPIVOT函数: SELECT * FROM #Students unpivot(score for subject in(Chiness,Math,...UNPIVOT函数和PIVOT函数从结构上书写形式基本一致,只是UNPIVOT不用聚合函数! 最终结果呈现形式如下:
  • 今天说的是SQLserver的pivot(行转列)和unpivot(列转行),废话不多说先来看一下语法, 首先为了找到合适的场景,我随机写了一个SQL,代码如下 SELECT business_type, COUNT(business_type) FROM dbo.pd_product...
  • In this article, we’ll walk-through the SQL Pivot and SQL Unpivot operators and how they can be useful to transpose SQL Server data. Also, we’ll discuss both static and dynamic ways t...
  • Hive版的Pivit和Unpivot实现方式

    千次阅读 2016-11-04 10:06:53
    Unpivot SELECT t1.uid, t2. key , t2. value FROM htable t1 LATERAL VIEW explode (map( 'c1' , c1, 'c2' , c2, 'c3' , c3 )) t2 as key , value uid key value 101 c1 11 101 c2 12 ...
  • 在不同的编程语言中有不同的实现方法,比如SQL中使用case+group,或者Power BI的M语言中用拖放组件实现。今天正好需要在pyspark中处理一个数据行列转换,就把这个方法记录下来。 首先明确一下啥叫行列转换,因为这...
  • sql 列转行 unpivot

    2018-09-16 00:37:09
    use db_sql2005 GO select * from tb_Sell unpivot(销售数量 for 月份 in([九月],[十月],[十一月],[十二月] )) as a 相当于: SELECT 商品名称, 九月 AS 销售数量, '九月' AS 月份 FROM tb_Sell GROUP BY ...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 3,323
精华内容 1,329
关键字:

sqlunpivot