pivot_pivot table - CSDN
精华内容
参与话题
  • pivot的用法

    万次阅读 2018-08-21 20:07:08
    在数据库操作中,有些时候我们遇到需要实现“行转列”的需求,例如一下的表为某店铺的一周收入情况表: WEEK_INCOME(WEEK VARCHAR(10),INCOME DECIMAL) 我们先插入一些模拟数据: INSERT INTO WEEK_INCOME ...

    在数据库操作中,有些时候我们遇到需要实现“行转列”的需求,例如一下的表为某店铺的一周收入情况表:

    WEEK_INCOME(WEEK VARCHAR(10),INCOME DECIMAL)

    我们先插入一些模拟数据:

    INSERT INTO WEEK_INCOME 
    SELECT '星期一',1000
    UNION ALL
    SELECT '星期二',2000
    UNION ALL
    SELECT '星期三',3000
    UNION ALL
    SELECT '星期四',4000
    UNION ALL
    SELECT '星期五',5000
    UNION ALL
    SELECT '星期六',6000
    UNION ALL
    SELECT '星期日',7000

     

    一般我们最经常使用的查询是查询一周中每天或某几天的收入,例如查询周一至周日全部的收入:

    SELECT WEEK,INCOME FROM WEEK_INCOME

    得到如下的查询结果集:

    WEEK           INCOME
    星期一           1000
    星期二           2000
    星期三           3000
    星期四           4000
    星期五           5000
    星期六           6000
    星期日           7000

     

    但是在一些情况下(往往是某些报表中),我们希望在一行中显示周一至周日的收入,这时候查询结果集应该是这样的:

    星期一   星期二   星期三   星期四   星期五   星期六   星期日
    1000     2000     3000     4000     5000     6000     7000

    这种情况下,SQL查询语句可以这样写:

    复制代码

    SELECT  
    SUM(CASE WEEK WHEN '星期一' THEN INCOME END) AS [星期一],
    SUM(CASE WEEK WHEN '星期二' THEN INCOME END) AS [星期二],
    SUM(CASE WEEK WHEN '星期三' THEN INCOME END) AS [星期三],
    SUM(CASE WEEK WHEN '星期四' THEN INCOME END) AS [星期四],
    SUM(CASE WEEK WHEN '星期五' THEN INCOME END) AS [星期五],
    SUM(CASE WEEK WHEN '星期六' THEN INCOME END) AS [星期六],
    SUM(CASE WEEK WHEN '星期日' THEN INCOME END) AS [星期日]
    FROM WEEK_INCOME

    复制代码

    但是,在SQL SERVER 2005中提供了更为简便的方法,这就是"PIVOT"关系运算符。(相反的“列转行”是UNPIVOT),以下是使用PIVOT实现“行转列”的SQL语句

    复制代码

    SELECT [星期一],[星期二],[星期三],[星期四],[星期五],[星期六],[星期日]
    FROM WEEK_INCOME
    PIVOT
    (
        SUM(INCOME) for [week] in([星期一],[星期二],[星期三],[星期四],[星期五],[星期六],[星期日])
    )TBL
    

    复制代码

     

     

    请参考MSDN中关于PIVOT的用法:

    http://technet.microsoft.com/zh-cn/library/ms177410(v=sql.105).aspx

     

    但是MSDN上的描述太过于规范严肃,我看了半天还没弄清楚怎样使用PIVOT,搞不清楚PIVOT里面的语法的含义。于是又google了很多资料,以及通过上面提到的WEEK_INCOME表例子作了试验,最终搞清楚了其用法。在网上有篇博文解释的很好:T-SQL PIVOT語法剖析與實戰,基本上我要写的就是参照该博文,再加上自己一点个人理解。

    要理解PIVOT语法,就是要清楚微软为什么这样设计PIVOT,但我相信是现实需求催生设计思路,所以归根到底我们还是要弄清楚什么是“行转列”:

    正常情况下的查询结果是这样:

    星期一           1000
    星期二           2000
    星期三           3000
    星期四           4000
    星期五           5000
    星期六           6000
    星期日           7000

    行转列后是这样:

    星期一   星期二   星期三   星期四   星期五   星期六   星期日
    1000    2000    3000    4000    5000    6000    7000

    也就是说,行转列后,原来的某个列的值变做了列名,在这里就是原来WEEK列的值“星期一”,"星期二"..."星期日"边做了列名,而我们需要做的另一个工作就是计算这些列的值(这里的“计算”其实就是PIVOT里面的聚合函数(sum,avg等))

    现在结合注释来分析一下PIVOT语法(在这之前最好看看我上面提到博文:T-SQL PIVOT語法剖析與實戰,里面说到的PIVOT语法的三个步骤挺重要):

    复制代码

    SELECT [星期一],[星期二],[星期三],[星期四],[星期五],[星期六],[星期日]--这里是PIVOT第三步(选择行转列后的结果集的列)这里可以用“*”表示选择所有列,也可以只选择某些列(也就是某些天)
    FROM WEEK_INCOME --这里是PIVOT第二步骤(准备原始的查询结果,因为PIVOT是对一个原始的查询结果集进行转换操作,所以先查询一个结果集出来)这里可以是一个select子查询,但为子查询时候要指定别名,否则语法错误
    PIVOT
    (
        SUM(INCOME) for [week] in([星期一],[星期二],[星期三],[星期四],[星期五],[星期六],[星期日])--这里是PIVOT第一步骤,也是核心的地方,进行行转列操作。聚合函数SUM表示你需要怎样处理转换后的列的值,是总和(sum),还是平均(avg)还是min,max等等。例如如果week_income表中有两条数据并且其week都是“星期一”,其中一条的income是1000,另一条income是500,那么在这里使用sum,行转列后“星期一”这个列的值当然是1500了。后面的for [week] in([星期一],[星期二]...)中 for [week]就是说将week列的值分别转换成一个个列,也就是“以值变列”。但是需要转换成列的值有可能有很多,我们只想取其中几个值转换成列,那么怎样取呢?就是在in里面了,比如我此刻只想看工作日的收入,在in里面就只写“星期一”至“星期五”(注意,in里面是原来week列的值,"以值变列")。总的来说,SUM(INCOME) for [week] in([星期一],[星期二],[星期三],[星期四],[星期五],[星期六],[星期日])这句的意思如果直译出来,就是说:将列[week]值为"星期一","星期二","星期三","星期四","星期五","星期六","星期日"分别转换成列,这些列的值取income的总和。
    )TBL--别名一定要写

    复制代码

     

    转自:https://www.cnblogs.com/lwhkdash/archive/2012/06/26/2562979.html

     

    还有一个一种情况,就是pivot后面跟着跟着很多的字段,这样就会进行拼接:

    比如下面的这个例子:

    with pivot_data as
       (select GY_FS,
               substr(xzq_dm, 1, 6) xzqbm,
               GD_ZMJ,
               xz_mj,
               je,
               xm_zt,
               pz_rq
          from TDGY_T_GDXM t
         where pz_rq between to_date('2010/3/1', 'yyyy/MM/dd') and
               to_date('2010/3/31', 'yyyy/MM/dd')
               and xm_zt like '2%'
              and xzq_dm not like '%0000000'--先过滤省级
           and xzq_dm not like '%00000' )--后过滤市级级 
      select xzqbm,
             sum(HB_ZDS) HB_ZDS,
             sum(HB_MJ) HB_MJ,
             sum(HB_XZ) HB_XZ,
             sum(ZPGCR_MJ) + sum(ZPGCR_ZB_MJ) + sum(ZPGCR_PM_MJ) + sum(ZPGCR_GP_MJ) ZPGCR_MJ,
             sum(ZPGCR_XZ) + sum(ZPGCR_ZB_XZ) + sum(ZPGCR_PM_XZ) + sum(ZPGCR_GP_XZ) ZPGCR_XZ,
             
             sum(ZPGCR_PM_MJ) ZPGCR_PM_MJ,
             sum(ZPGCR_PM_XZ) ZPGCR_PM_XZ,
             sum(ZPGCR_PM_CJJK) ZPGCR_PM_CJJK,
             sum(ZPGCR_GP_ZDS) ZPGCR_GP_ZDS,
             sum(ZPGCR_GP_MJ) ZPGCR_GP_MJ,
             sum(ZPGCR_GP_XZ) ZPGCR_GP_XZ,
             sum(ZPGCR_GP_CJJK) ZPGCR_GP_CJJK,
             sum(XYCR_ZDS) XYCR_ZDS,
             sum(XYCR_MJ) XYCR_MJ,
             sum(XYCR_XZ) XYCR_XZ,
             sum(XYCR_CJJK) XYCR_CJJK,
    		 sum(ZL_ZDS) ZL_ZDS,
             sum(ZL_MJ) ZL_MJ,
             sum(ZL_XZ) ZL_XZ,
             sum(ZL_CJJK) ZL_ZJ,
    	
             sum(SQJY_CJJK) SQJY_SR
        from pivot_data
      pivot (count(*) ZDS, sum(GD_ZMJ) MJ, sum(xz_mj) XZ, sum(JE) CJJK for GY_FS in('1' as HB,
                                                                                '2' as ZPGCR,
                                                                                '21' as ZPGCR_ZB,
                                                                                '22' as ZPGCR_PM,
                                                                                '23' as ZPGCR_GP,
                                                                                '3' as XYCR,
                                                                                '4' as ZL,
                                                                                '5' as ZJCZHRG,
                                                                                '6' as SQJY
                                          ))
       group by xzqbm
       order by xzqbm

    如上代码,oracle中会把后面的在in中的字段和前面定义的ZDS、MJ、XZ等字段进行拼接显示。

    展开全文
  • python pandas库——pivot使用心得

    万次阅读 多人点赞 2017-12-14 17:24:46
    在做数据统计二维表转换的时候走了不少弯路,发现pivot()这个方法可以解决很多问题,让我少走一些弯路,节省了大量的代码。于是我这里对于pandas下dataframe的pivot()方法进行学习总结和应用,以便回顾和巩固知识。

    最近在做基于python的数据分析工作,引用第三方数据分析库——pandas(version 0.16)。
    在做数据统计二维表转换的时候走了不少弯路,发现pivot()这个方法可以解决很多问题,让我少走一些弯路,节省了大量的代码。于是我这里对于pandas下dataframe的pivot()方法进行学习总结和应用,以便回顾和巩固知识。


    以统计学生成绩信息为例。
    在做学生成绩信息统计的时候,我们从学生各科考试成绩文件(.csv或.xls等)中把数据抽取上来。样本模拟数据(data_df)如下。

    In [13]: print data_df
      userNum  score subjectCode subjectName userName
    0   001     90        01         语文       张三
    1   002     96        01         语文       李四
    2   003     93        01         语文       王五
    3   001     87        02         数学       张三
    4   002     82        02         数学       李四
    5   003     80        02         数学       王五
    

    要把上面二维表转换为每个人各科的成绩信息。就像咱们中学时期的成绩单一样。类似于

    学籍号  姓名  班级  语文成绩  语文排名  数学成绩  数学排名
                            ...
    

    的一张二维表。

    我之前的传统统计方式,给data_df根据学籍号进行groupby,再循环遍历该分组得到每个人的各科成绩信息,再统计到一张新表中,然后循环append每一张新表,可生成以上的样表。如果我们需要统计全年级的学生呢?可能一个年级有500个学生,那就是循环500次。此时我们需要统计一个市区内多校联考的学生呢?岂不是要循环成百上千次?实际情况,这样的做法使得我们的脚本跑的非常的慢。

    直到我在pandas的官方api上查到pivot()的这个方法。
    pandas给pivot的官方解释

    大概的意思就是根据列对数据表进行重塑。这样理解实在晦涩难懂。我不喜欢长篇大论,更喜欢暴力一点的,use it and 直观感受它(这样做当然不可取,最好还是对它的方法理解透彻一些,以便了解他更多的适用场景)。

    从官方api可以知道他有三个参数,第一个index是重塑的新表的索引名称是什么,第二个columns是重塑的新表的列名称是什么,一般来说就是被统计列的分组,第三个values就是生成新列的值应该是多少,如果没有,则会对data_df剩下未统计的列进行重新排列放到columns的上层。

    直接上代码

    In [20]: pivot_df = data_df.pivot(index='userNum', columns='subjectCode', values='score')
    

    我们给能标识每个学生的学籍号userNum作为索引,因为我们是要统计每个学生,所以每个学生的信息作为一行。要生成语文成绩,数学成绩等,那么可以用标识学科的subjectCode作为每一列,最后,值,当然就是score给每个科目赋成绩值了!

    以下是生成的结果

    In [21]: print pivot_df
    subjectCode  01  02
    userNum
    001          90  87
    002          96  82
    003          93  80
    

    这就生成了我们大致想要的样子了,之后可以再给pivot_df的列名进行调整,还有其整体样式的调整。

    # 这只是其中一个方式,如有更好的方式,不吝赐教~
    
    # 列名称置空
    pivot_df.columns.name = None
    # 遍历每个学科对新表列名进行修改
    data_df_G = data_df.groupby(["subjectCode"], as_index=False)
    temp_count = 1
    for index, subject_df in data_df_G:
        # 把成绩排名添加到各科成绩之后
        pivot_df.insert(temp_count, "rank_" + str(index), pivot_df[index].rank(ascending=False, method='min'))
        # 重命名各科成绩
        pivot_df.rename(columns={index: ("score_" + str(index))}, inplace=True)
        temp_count += 2
    # 把userNum添加的列中
    pivot_df['userNum'] = pivot_df.index
    # 索引名称置空
    pivot_df.index.name = None
    
    temp_df = data_df.loc[:, ["userNum", "userName"]]
    temp_df.drop_duplicates(inplace=True)
    # 剩余列拼接
    pivot_df = temp_df.merge(pivot_df, on="userNum", how="left")

    最后生成的样式,大致能满足我们需要的东西了

    In [30]: print(pivot_df)
      userNum userName  score_01  rank_01  score_02  rank_02
    0   001       张三      90        3        87        1
    1   002       李四      96        1        82        2
    2   003       王五      93        2        80        3
    

    文中若有表述不当或实现不妥的地方,欢迎指正!

    展开全文
  • PIVOT详解

    2010-11-05 18:49:00
    PIVOT查询把数据从行转换到列,UNPIVOT查询把数据从列转换为行。   无论是SQL Server2000还是SQL Server2005中,静态的PIVOT和UNPIVOT查询都不能处理未知数量的被旋转的元素。   例如:...

    PIVOT查询把数据从行转换到列,UNPIVOT查询把数据从列转换为行。

     

    无论是SQL Server2000还是SQL Server2005中,静态的PIVOT和UNPIVOT查询都不能处理未知数量的被旋转的元素。

     

    例如:

     

    查询出来的结果如下:

     

    使用PIVOT:

     

    输出结果:

     

    但是必须你得知道只有[2002],[2003],[2004]这几种情况

    展开全文
  • 本文转载自知乎《Pandas | 一文看懂透视表pivot_table》,在原文基础上略有增删改。感谢原作者非常生动的例子。 目录 一、概述 1.1 什么是透视表? 1.2 为什么要使用pivot_table? 二、如何使用pivot_table ...

    本文转载自知乎《Pandas | 一文看懂透视表pivot_table》,在原文基础上略有增删改。感谢原作者非常生动的例子。


    目录

    一、概述

    1.1 什么是透视表?

    1.2 为什么要使用pivot_table?

    二、如何使用pivot_table

    2.1 读取数据

    2.2 Index

    2.3 Values

    2.4 Aggfunc

    2.5 Columns

    2.6 pivot_table vs. groupby

    2.7 query

    2.8 Cheat Sheet


    一、概述

    1.1 什么是透视表?

    透视表是一种可以对数据动态排布并且分类汇总的表格格式。或许大多数人都在Excel使用过数据透视表,也体会到它的强大功能,而在pandas中它被称作pivot_table。

    1.2 为什么要使用pivot_table?

    • 灵活性高,可以随意定制你的分析计算要求
    • 脉络清晰易于理解数据
    • 操作性强,报表神器

    二、如何使用pivot_table

    首先读取数据,数据集是火箭队当家球星James Harden某一赛季比赛数据作为数据集进行讲解。数据地址

    先看一下官方文档中pivot_table的函数体:pandas.pivot_table - pandas 0.21.0 documentation

    pivot_table(datavalues=Noneindex=Nonecolumns=None,aggfunc='mean'fill_value=Nonemargins=Falsedropna=Truemargins_name='All')

    pivot_table有四个最重要的参数index、values、columns、aggfunc,本文以这四个参数为中心讲解pivot操作是如何进行。

    2.1 读取数据

    import pandas as pd
    import numpy as np
    df = pd.read_csv('h:/James_Harden.csv',encoding='utf8')
    df.tail()

    数据格式如下:

    2.2 Index

    每个pivot_table必须拥有一个index如果想查看哈登对阵每个队伍的得分,首先我们将对手设置为index

    pd.pivot_table(df,index=[u'对手'])

    对手成为了第一层索引,还想看看对阵同一对手在不同主客场下的数据,试着将对手胜负主客场都设置为index

    pd.pivot_table(df,index=[u'对手',u'主客场'])

    试着交换下它们的顺序,数据结果一样:

    pd.pivot_table(df,index=[u'主客场',u'对手'])

    看完上面几个操作,Index就是层次字段,要通过透视表获取什么信息就按照相应的顺序设置字段,所以在进行pivot之前你也需要足够了解你的数据。

    2.3 Values

    通过上面的操作,我们获取了james harden在对阵对手时的所有数据,而Values可以对需要的计算数据进行筛选,如果我们只需要james harden在主客场和不同胜负情况下的得分、篮板与助攻三项数据:

    pd.pivot_table(df,index=[u'主客场',u'胜负'],values=[u'得分',u'助攻',u'篮板'])

    2.4 Aggfunc

    aggfunc参数可以设置我们对数据聚合时进行的函数操作。

    当我们未设置aggfunc时,它默认aggfunc='mean'计算均值。我们还想要获得james harden在主客场和不同胜负情况下的得分、篮板、助攻时:

    pd.pivot_table(df,index=[u'主客场',u'胜负'],values=[u'得分',u'助攻',u'篮板'],aggfunc=[np.sum,np.mean])

    2.5 Columns

    Columns类似Index可以设置列层次字段,它不是一个必要参数,作为一种分割数据的可选方式。

    #fill_value填充空值,margins=True进行汇总
    pd.pivot_table(df,index=[u'主客场'],columns=[u'对手'],values=[u'得分'],aggfunc=[np.sum],fill_value=0,margins=1)

    现在我们已经把关键参数都介绍了一遍,下面是一个综合的例子:

    table=pd.pivot_table(df,index=[u'对手',u'胜负'],columns=[u'主客场'],values=[u'得分',u'助攻',u'篮板'],aggfunc=[np.mean],fill_value=0)

    结果如下:

    aggfunc也可以使用dict类型,如果dict中的内容与values不匹配时,以dict中为准。

    table=pd.pivot_table(df,index=[u'对手',u'胜负'],columns=[u'主客场'],values=[u'得分',u'助攻',u'篮板'],aggfunc={u'得分':np.mean,
                         u'助攻':[min, max, np.mean]},fill_value=0)

    结果就是助攻求min,max和mean,得分求mean,而篮板没有显示。

    2.6 pivot_table vs. groupby

    通过以上,可以看到pivot_table的用法与Groupby很相近。

    pd.pivot_table(df,index=[字段1],values=[字段2],aggfunc=[函数],fill_value=0)
    df.groupby([字段1])[字段2].agg(函数).fillna(0)
    

    上面两个函数完全等价,pivot_table仿佛是加入了columns与margin功能的groupby函数,比groupby更加灵活

    2.7 query

    当表格生成后如何查询某一项数据呢?

    ex.根据上表查询哈登对阵灰熊时的数据

    table.query('对手 == ["灰熊"]')

    2.8 Cheat Sheet

     

    展开全文
  • Pivot 和 Unpivot 使用简单的 SQL 以电子表格类型的交叉表报表显示任何关系表中的信息,并将交叉表中的所有数据存储到关系表中。 如您所知,关系表是表格化的,即,它们以列-值对的形式出现。假设一个表名为 ...
  • 首先感谢提供Oracle的行列转换丝路!!!
  • SQL使用 PIVOT 和 UNPIVOT

    千次阅读 2019-04-16 16:06:13
    可以使用 PIVOT 和 UNPIVOT 关系运算符将表值表达式更改为另一个表。PIVOT 通过将表达式某一列中的唯一值转换为输出中的多个列来旋转表值表达式,并在必要时对最终输出中所需的任何其余列值执行聚合。UNPIVOT 与 ...
  • PIVOT 用法详解

    千次阅读 2010-01-19 17:01:00
    PIVOT 和 UNPIVOT 关系运算符将表值表达式更改为另一个表。PIVOT 通过将表达式某一列中的唯一值转换为输出中的多个列来旋转表值表达式,并在必要时对最终输出中所需的任何其余列值执行聚合。 PIVOT 提供的语法比一()...
  • PIVOT 和 UNPIVOT实例使用

    千次阅读 2019-07-28 03:21:34
    PIVOT 通过将表达式某一列中的唯一值转换为输出中的多个列来旋转表值表达式,并在必要时对最终输出中所需的任何其余列值执行聚合。UNPIVOT 与 PIVOT 执行相反的操作,将表值表达式的列转换为列值。 1、PIVOT 使用...
  • Oracle 行转列 pivot函数基本用法

    万次阅读 2019-01-23 09:19:18
    2018年9月30日22点,眼看着就10月份了,回头看下,8月份就写了一...所以暂时先写个Oracle自带的行转列函数,pivot的基本用法。国庆几天看下有时间的话完善一下动态转列的做法,到时候再另写一篇附链接过来。 一、运...
  • MySQL实现pivot行转列

    万次阅读 2019-04-11 08:39:42
    1.创建表: Create Table CREATETABLE`pivot`( `id`int(11)NOTNULLAUTO_INCREMENT, `salary`doubleNOTNULL, `year`varchar(4)NOTNULL, `name`varchar(10)NOTNULL, PRI...
  • 想用Unity来做一些动画,用到Pivot来调整中点作为旋转点。但是回到家发现,Pivot那个蓝色小圈圈怎么都拖不动。 也不知道是怎么回事,就放着放了两天,今天到Google搜索到相关问题...
  • 【Unity】Unity Pivot 与 Center 的问题

    千次阅读 2017-07-28 14:50:39
    首先 说一下 Pivot 与 Center pivot就是模型坐标轴的真实位置,也就是说再模型软件中设定的坐标轴。center是unity自己根据模型的mesh计算的中心位置,和模型真实坐标轴没关系了。 transform.position 的坐标轴就是...
  • Power Pivot VS Excel Power Pivot 与 Power BI 其他组件的关系 图解用Power Pivot 创建一个简单的多表数据模型并生成Power View报告 导入源数据 创建关系 两种方法,如以下两图所示: *多端...
  • Excel-如何用VBA编程操作Pivot Table

    万次阅读 2015-05-27 09:43:04
    Excel的Pivot Table在现实的工作中经常使用到,也常常需要用VBA来自动操作Pivot Table。 最近发现不错的代码样例,特摘抄供大家参考学习。 Create A Pivot Table Sub CreatePivotTable() 'PURPOSE: ...
  • Power Pivot连接多表关联

    千次阅读 2019-11-28 16:33:00
    1.打开文件 2.Ctrl+T 生成表 3.修改表名 4.-POWERPIVOT -添加到数据模型 5.创建关系图视图 6.点击拖动,使两个列进行关联 7.-点击开始-数据透视图 8.完成 ...
  • Python dataframe.pivot()

    千次阅读 2017-09-06 21:21:00
    简而言之,我理解的pivot()的用途就是,将一个dataframe的记录数据整合成表格,而且是按照pivot(‘index=xx’,’columns=xx’,’values=xx’)来整合的。还有另外一种写法,但是官方貌似并没有给出来,就是pivot(...
  • 假设共显示3类数据,先选择两类数据 选择完数据域后,选择2-D Line 里的折线图 初步形成的Chart是这样的 然后,鼠标悬停在折线图中,右键,选择 Select Data 选项 在弹出的对话框中选择 Add 选项 点击之后,新...
  • 如何使用 powerquery 和 powerpivot 在 Excel 中实现数据可视化标签: 数据可视化,powerquery, powerpivot , excel使用的软件 Power Pivot Power Query add-in powerbi erdplus 在 Excel 的文件->选项->加载项->COM...
  • ## 参数 sort_index(axis=0, level=None, ascending=True, inplace=False, kind='quicksort', na_position='last', sort_remaining=True, by=None) ## 参数说明 axis:0按照行名排序;1按照列名排序 ...
1 2 3 4 5 ... 20
收藏数 62,247
精华内容 24,898
关键字:

pivot