精华内容
下载资源
问答
  • 在报表页面利用HTML控件添加DIV层,并设置DIV宽度和高度,通过JS获取页面中列表或交叉表对象,列表和交叉表均为table控件,通过JS对表行或列进行操作。
  • SQL完成交叉表功能

    2012-08-08 09:38:45
    SQL完成交叉表功能 转自http://www.2cto.com/database/201111/112122.html   交叉表(Cross Tabulations)是一种常用分类汇总表格。使用交叉表查询,显示源于表中某个字段汇总值,并将它们分组,其中一组...

    SQL完成交叉表功能

    转自http://www.2cto.com/database/201111/112122.html

     

    交叉表(Cross Tabulations)是一种常用的分类汇总表格。使用交叉表查询,显示源于表中某个字段的汇总值,并将它们分组,其中一组列在数据表的左侧,另一组列在数据表的上部。行和列的交叉处可以对数据进行多种汇总计算,如:求和、平均值、记数、最大值、最小值等。(摘自百度百科)

    交叉报表是指提取数据并将其变换成列名称,从而使数据能够以交叉部分格式进行编排的表格形式。交叉报表是报表当中常见的类型,属于基本的报表,是行、列方向都有分组的报表。这里牵涉到另外一个概念即分组报表。这是所有报表当中最普通,最常见的报表类型,也是所有报表工具都支持的一种报表格式。从一般概念上来讲,分组报表就是只有纵向的分组。传统的分组报表制作方式是把报表划分为条带状,用户根据一个数据绑定向导指定分组,汇总字段,生成标准的分组报表。(摘自智库百科)

     

    假设有张学生成绩表(tb)如下:

    /*

    Name Subject Result

    张三 语文  74

    张三 数学  83

    张三 物理  93

    李四 语文  74

    李四 数学  84

    李四 物理  94

    */

     

    -------------------------------------------------------------------------

    /*

    想变成

    姓名 语文 数学 物理

    ---------- ----------- ----------- -----------

    李四 74 84 94

    张三 74 83 93

    */

     

     

    create table tb
    
    (
    
    Name varchar(10) ,
    
    Subject varchar(10) ,
    
    Result int
    
    )
    
    
    insert into tb(Name , Subject , Result) values('张三' , '语文' , 74)
    
    insert into tb(Name , Subject , Result) values('张三' , '数学' , 83)
    
    insert into tb(Name , Subject , Result) values('张三' , '物理' , 93)
    
    insert into tb(Name , Subject , Result) values('李四' , '语文' , 74)
    
    insert into tb(Name , Subject , Result) values('李四' , '数学' , 84)
    
    insert into tb(Name , Subject , Result) values('李四' , '物理' , 94)
    
    go
    
    

     --静态SQL,指subject只有语文、数学、物理这三门课程。

    select name 姓名,
    
    max(case subject when '语文' then result else 0 end) 语文,
    
    max(case subject when '数学' then result else 0 end) 数学,
    
    max(case subject when '物理' then result else 0 end) 物理
    
    from tb
    
    group by name
    
    

     /*

    姓名 语文 数学 物理

    ---------- ----------- ----------- -----------

    李四 74 84 94

    张三 74 83 93

    */

     

    --动态SQL,指subject不止语文、数学、物理这三门课程。

    declare @sql varchar(8000)
    
    set @sql = 'select Name as ' + '姓名'
    
    select @sql = @sql + ' , max(case Subject when ''' + Subject + ''' then Result else 0 end) [' + Subject + ']'
    
    from (select distinct Subject from tb) as a
    
    set @sql = @sql + ' from tb group by name'
    
    exec(@sql)
    
    

     /*

    姓名 数学 物理 语文

    ---------- ----------- ----------- -----------

    李四 84 94 74

    张三 83 93 74

    */

     

    -------------------------------------------------------------------

    /*加个平均分,总分

    姓名 语文 数学 物理 平均分 总分

    ---------- ----------- ----------- ----------- -------------------- -----------

    李四 74 84 94 84.00 252

    张三 74 83 93 83.33 250

    */

     

    --静态SQL,指subject只有语文、数学、物理这三门课程。

    select name 姓名,
    
    max(case subject when '语文' then result else 0 end) 语文,
    
    max(case subject when '数学' then result else 0 end) 数学,
    
    max(case subject when '物理' then result else 0 end) 物理,
    
    cast(avg(result*1.0) as decimal(18,2)) 平均分,
    
    sum(result) 总分
    
    from tb
    
    group by name
    
    

     /*

    姓名 语文 数学 物理 平均分 总分

    ---------- ----------- ----------- ----------- -------------------- -----------

    李四 74 84 94 84.00 252

    张三 74 83 93 83.33 250

    */

     

    --动态SQL,指subject不止语文、数学、物理这三门课程。

    declare @sql1 varchar(8000)
    
    set @sql1 = 'select Name as ' + '姓名'
    
    select @sql1 = @sql1 + ' , max(case Subject when ''' + Subject + ''' then Result else 0 end) [' + Subject + ']'
    
    from (select distinct Subject from tb) as a
    
    set @sql1 = @sql1 + ' , cast(avg(result*1.0) as decimal(18,2)) 平均分,sum(result) 总分from tb group by name'
    
    exec(@sql1)
    
    

     /*

    姓名 数学 物理 语文 平均分 总分

    ---------- ----------- ----------- ----------- -------------------- -----------

    李四 84 94 74 84.00 252

    张三 83 93 74 83.33 250

    */

     

    drop table tb

     

    ---------------------------------------------------------

    ---------------------------------------------------------

    /*

    如果上述两表互相换一下:即

     

    姓名 语文 数学 物理

    张三74  83  93

    李四74  84  94

     

    想变成

    Name Subject Result

    ---------- ------- -----------

    李四 语文 74

    李四 数学 84

    李四 物理 94

    张三 语文 74

    张三 数学 83

    张三 物理 93

    */

     

    create table tb1
    
    (
    
    姓名varchar(10) ,
    
    语文int ,
    
    数学int ,
    
    物理int
    
    )
    
    
    insert into tb1(姓名, 语文, 数学, 物理) values('张三',74,83,93)
    
    insert into tb1(姓名, 语文, 数学, 物理) values('李四',74,84,94)
    
    
    select * from
    
    (
    
    select 姓名as Name , Subject = '语文' , Result = 语文from tb1
    
    union all
    
    select 姓名as Name , Subject = '数学' , Result = 数学from tb1
    
    union all
    
    select 姓名as Name , Subject = '物理' , Result = 物理from tb1
    
    ) t
    
    order by name , case Subject when '语文' then 1 when '数学' then 2 when '物理' then 3 when '总分' then 4 end
    

     

     

    --------------------------------------------------------------------

    /*加个平均分,总分

    Name Subject Result

    ---------- ------- --------------------

    李四 语文 74.00

    李四 数学 84.00

    李四 物理 94.00

    李四 平均分 84.00

    李四 总分 252.00

    张三 语文 74.00

    张三 数学 83.00

    张三 物理 93.00

    张三 平均分 83.33

    张三 总分 250.00

    */

     

     

    select * from
    
    (
    
    select 姓名as Name , Subject = '语文' , Result = 语文from tb1
    
    union all
    
    select 姓名as Name , Subject = '数学' , Result = 数学from tb1
    
    union all
    
    select 姓名as Name , Subject = '物理' , Result = 物理from tb1
    
    union all
    
    select 姓名as Name , Subject = '平均分' , Result = cast((语文+ 数学+ 物理)*1.0/3 as decimal(18,2)) from tb1
    
    union all
    
    select 姓名as Name , Subject = '总分' , Result = 语文+ 数学+ 物理from tb1
    
    ) t
    
    order by name , case Subject when '语文' then 1 when '数学' then 2 when '物理' then 3 when '平均分' then 4 when '总分' then 5 end
    
    

     drop table tb1

    展开全文
  • 目录背景调查问卷的目的交叉表的使用展现形式重要指标的细分假设检验的引入利用Python生成交叉表获得原始数据表格生成逻辑图代码示例结果分析小结 背景 现如今,社交媒体已成为各大品牌商公认的重要营销渠道。品牌商...

    背景

    现如今,社交媒体已成为各大品牌商公认的重要营销渠道。品牌商们除了在各类社交平台建立自己的营销账号外,每年还会花费数以亿计的资金与知名的媒体节目,明星,自媒体们合作。虽然相较于传统营销渠道,社交媒体透过流量的红利有着低成本高触达的优点,但高度密集的营销信息也让人们逐渐失去了互动耐心,高成本的广告投资常常仅换来社交用户几秒钟的注意力,所以如何利用好社交媒体海量的数据信息十分重要。

    一般情况下,用户的行为分析主要分为三类:1. 用户数据分析与监控 2.调查问卷 3. 访谈。其中第一类和第二类是了解用户需求比较常见的方式。今天我主要想聊一聊第二类方式的目的以及知名品牌商们比较青睐的结果展现形式。

    调查问卷的目的

    尽管社交媒体本身具备快速获取海量数据的优点,但仅通过用户数据本身并不能百分百反应用户的真实需求。而一份调查面广,靶点清晰的用户调研问卷可以在一定程度上弥补这一缺点。例如,Dropbox在推广早期升级版本时有过一段很长时间的增长瓶颈期,数据分析师们从用户的使用数据中发现,大多数活跃用户都没有使用升级版本。他们不断推出试用版本,不断优化升级版的用户体验,但仍收效甚微。直到Dropbox引进了新的增长团队,做了一次用户调研,才发现大部分的用户根本不知道Dropbox推出了升级版本,新版本的试用引导路线也十分不明确。产品部这才意识到,增长的起点并不是升级版本身,而是升级版本需要有更加直观清晰的引导策略。

    对于社交媒体营销而言,用户调研问卷也行使着相似的功能,品牌商们也可从中了解到营销广告让人过目不忘的程度,用户们的接受程度,验证营销合作对象对用户群体的实际影响价值等等。这种做法可以让产品以及市场人员对需求与业务流程建立更直观的认识并且更容易获得一些常被忽略的细节。

    交叉表的使用

    相较于透视表,交叉表更适合于呈现复杂且多维度的用户调研结果。同一个问题,可以在不同维度下展示。例如,我想知道,在过去的两天中,用户是否能回想起我们公司与某媒体合作的广告。除了了解整体数据,将这个问题放在多个维度下去解析会更加准确且快速地察觉到不同用户画像间的区别 —— 我可以了解到参与了营销博文互动的用户是否对广告的印象更深刻,又或是,参与了互动且能回想起营销内容的25岁以上的用户是否更喜欢我所选择的营销合作对象,等等。

    以下就是一种常见的用户调研结果的展现形式。除了每个维度下的统计数据,我们还可以直接计算出问题中每个选项的横向与纵向占比。
    在这里插入图片描述

    假设检验的引入

    但如何才能验证参与了营销互动的用户比未参与互动的用户对广告的印象更深刻呢?

    假设检验以及卡方分析就是一种比较常见的,用来研究两个定类变量间是否独立即是否存在某种关联性的方法。例如,针对第一个问题,Null Hypothesis可以设定为:是否参与营销博文互动与广告让人过目不忘的程度之间没有关联。而我们可以引入p-value(假设检验为真的概率)来验证假设是否成立。

    在计算P-value之前,我们需要设定significance level, 当P-value小于该significance level时,我们拒绝原假设,证明两者之间有关联。

    利用Python生成交叉表
    获得原始数据

    以下是一个简单的样本数据,每一列都代表一个问题(维度)。
    在这里插入图片描述

    表格生成逻辑图

    ① 简单的二联表
    在这里插入图片描述
    ② 稍复杂的三联表
    在这里插入图片描述

    代码示例

    1. 获得原始数据

    import pandas as pd
    from scipy.stats import chi2_contingency
    from scipy.stats import chi2
    import numpy as np
    import warnings
    warnings.filterwarnings("ignore")
    
    # General Purpose Crosstab Generator (GPCG)
    
    def dataRead(File):
        
        global data,data1,data2    #将变量设为全局变量
        data = pd.read_excel(File)
        
    #    data = data[data.gender == 'Female']
        data1 = data[data.engager == "No"]
        data2 = data[data.engager == "Yes"]
    
        global y
        global z
        y = data['recall']
        z = data['engager']
        
    dataRead("Survey.xlsx")
    

    2. 交叉表的生成 -- ① 简单二联表
    根据以上的示例图,我们将年龄表格左侧的第一个维度,是否记得营销广告作为分组的第二个维度。

    ## ① ## 
    ## 建立一个简单的交叉表,且横向维度只有Yes和No
    def type1(question,fix,Index):
        a = pd.crosstab(question, fix, margins=True)
        a['Non'] = a['No']
        a['Yep'] = a['Yes']
        a = a.drop('No',axis=1)
        a = a.drop('Yes',axis=1)
        a = a.drop('All', axis = 1)
        a = a.fillna(0)
        a = a.reindex(Index)  #按照想要的方式排序
        a.to_clipboard(excel=True)
        return a
    

    在这里插入图片描述

    ## ② ## 
    ## 在简单的交叉表下方添加假设检验的结果
    def p_val(val):
        if val < 0.05:
            return "<0.05"
        elif val < 0.1:
            return "<0.1"
        elif val < 0.15:
            return "<0.15"
        else:
            return ">0.15"
        
    def chsq_test(question,c,Index):
        chsq_rlt=[]
        C=type1(question,c,Index)
        C1=C.iloc[1:,[0,1]]
        # remove all zeroes
        try:
            C1=C1[(C1.T != 0).any()]
            chsq_rlt.append(p_val(chi2_contingency(C1)[1]))
            # rename the column of the dataframe in order to merge p-vlue with crosstab
            b = pd.DataFrame(chsq_rlt).transpose()
            b["Non"] = b[0]
            b["Yep"] = ""            
            b = b.drop([0], axis = 1)
            b = b.rename({0:"p-value"})
            C = C.append(b)
        except:
            pass   
        return C
    

    在这里插入图片描述

    ## ③ ## 
    ## 按照交叉表模板添加每个选项所对应的百分比
    ## convert numbers into percentage
    def percent(X):
        A=X
        L=[[]]
        for i in range(0,len(A[0])):
            L[0].append("{:.1%}".format(A[0][i]))
        return np.array(L)
        
    def percentage1(question1,question2,Index_list,order):
        all_data=pd.DataFrame()
        p=chsq_test(question1,question2,Index_list)
        p=p.reset_index()
        List=p['index'].to_list()[:-1]
        if Text in Text_List:
            for i in range(1,len(p)-1):
                all_data=all_data.append(p[i:i+1])
                all_data=all_data.append(pd.DataFrame(percent(p[i:i+1].iloc[:,1:3].values / p[0:1].iloc[:,1:3].values), columns=p.iloc[:,1:3].columns))
                all_data=all_data.fillna('vert'+'%'+List[i])
                all_data=all_data.append(pd.DataFrame(percent(p[i:i+1].iloc[:,1:3].values / p[i:i+1].iloc[:,1:3].values.sum()), columns=p.iloc[:,1:3].columns))
                all_data=all_data.fillna('hori'+'%'+List[i])
            all_data=all_data.append(p[len(p)-1:len(p)])
            return  all_data[order]
    

    在这里插入图片描述
    在计算每一个问题所对应的的百分比时,有两点需要注意。1. DataFrame在做行与行之间的计算时,需要将数据格式改为Series。 2. 将结果转化为百分比时,可以采用"{:.1%}".format(A)。

    2. 交叉表的生成 -- ② 简单三联表
    其实三联表与二联表不用的地方在于我们需要将数据事先分为两组,生成两组索引相同的交叉表,然后再拼接起来。

    ## ① ## 
    ## 建立稍复杂的交叉表,且横向有两种维度(选项为两种维度的排列组合)
    # 1. 只需要将数据集按照是否参与营销互动分成两个数据集,分别利用type1()生成各自的交叉表
    # 2. 将两个交叉表拼接起来
    data1=data[data.engager=='No'] #根据是否参与营销互动,将数据分为的两部分
    data1=data[data.engager=='Yes']
    def combination1(question_str,Index_list):
        group = []
        groupings = [(data1, y), (data2, y)]
        keyGroup = ['Non-Engagers / Recall', 'Engagers / Recall']
        for element in groupings:
            group.append(chsq_test(element[0][question_str], element[1],Index_list))    
        result = pd.concat(group, keys = keyGroup, axis = 1)
        
        result = result.fillna(0)
        result.to_clipboard(excel=True)
        return(result)
    

    在这里插入图片描述

    ## ② ## 
    ## 计算行、列百分比
    def percentage3(question,Text,Index_list):
        all_data=pd.DataFrame()
        p=combination1(question,Index_list)
        p=p[Text].reset_index()
        List=p['index'].to_list()[:-1]
        for i in range(1,len(p)-1):
            all_data=all_data.append(p[i:i+1])
            all_data=all_data.append(pd.DataFrame(percent(p[i:i+1].iloc[:,1:5].values / p[0:1].iloc[:,1:5].values), columns=p.iloc[:,1:5].columns))
            all_data=all_data.fillna('vert'+'%'+List[i])
            all_data=all_data.append(pd.DataFrame(percent(p[i:i+1].iloc[:,1:5].values / p[i:i+1].iloc[:,1:5].values.sum()), columns=p.iloc[:,1:5].columns))
            all_data=all_data.fillna('hori'+'%'+List[i])
        all_data=all_data.append(p[len(p)-1:len(p)])
        return  all_data[['index','Non-Engagers / Recall','Engagers / Recall']]
    

    在这里插入图片描述
    2. 交叉表的生成 -- ③ 复杂二联表
    当二联表中每个维度所涉及的分组越多时,越有可能会出现某些选项的统计结果为零的情况,但我们仍然要将这些结果展现在表格里。但这就意味着我们的交叉表中会有至少一列的数据全为0,那么这种情况我们是无法计算P-value的。所以以下的例子中不含P-value的结果。

    ## ① ## 
    ## 建立有分组的简单的交叉表,且横向一种维度(几个选项)
    
    def type2(question,fix,Index_list,col_name,options):
        a = pd.crosstab(question, fix, margins=True)
        for i in options:
            if i in a.columns: 
                pass
            else: #如果某个选项没有数据,那就将其结果设为0
                a[i]=0 
        a['Total positives'] = a['Much more positive']+a['Somewhat more positive']+a['No Change, was already positive']
        a['Somewhat/ much more positive'] = a['Much more positive']+a['Somewhat more positive']
        a['Total Negatives'] = a['Much more negative']+a['Somewhat more negative']+a['No change, was already negative']
        a['Somewhat/ much more negative'] = a['Much more negative']+a['Somewhat more negative']
        ## 将列名依照要求排列
    
        a = a[col_name]
        a = a.fillna(0)
        a = a.reindex(Index_list)
        a.to_clipboard(excel=True)
        return a
    

    在这里插入图片描述

    ## ② ## 计算P-value
    
    def chsq_test2(question,c,Index_list,col_name,options):
        chsq_rlt=[]
        C=type2(question,c,Index_list,col_name,options)
        C1=C.iloc[1:,:]
        # remove all zeroes
        try:
            C1=C1[(C1.T != 0).any()]
            chsq_rlt.append(p_val(chi2_contingency(C1)[1]))
            # rename the column of the dataframe in order to merge p-vlue with crosstab
            b = pd.DataFrame(chsq_rlt).transpose()
            b[col_name[0]] = b[0]
            b = b.fillna(" ")
            b = b.rename({0:"p-value"})
            C = C.append(b)
        except:
            pass   
        return C[col_name]
    
    def percentage2(question,c,Index_list,col_name,options):
        all_data=pd.DataFrame()
        p=chsq_test2(question,c,Index_list,col_name,options)
        p=p.reset_index()
        if 'p-value' in p.iloc[:,0].to_list():
            List=p.iloc[:,0].to_list()[:-1]
            for i in range(1,len(p)-1):
                all_data=all_data.append(p[i:i+1])
                all_data=all_data.append(pd.DataFrame(percent(p[i:i+1].iloc[:,1:len(col_name)+1].values / p[0:1].iloc[:,1:len(col_name)+1].values), columns=p.iloc[:,1:len(col_name)+1].columns))
                all_data=all_data.fillna('vert'+'%'+List[i])
                all_data=all_data.append(pd.DataFrame(percent(p[i:i+1].iloc[:,1:len(col_name)+1].values / p[i:i+1].iloc[:,[1,3]].values.sum()), columns=p.iloc[:,1:len(col_name)+1].columns))
                all_data=all_data.fillna('hori'+'%'+List[i])
            all_data=all_data.append(p[len(p)-1:len(p)])
        else:
            List=p.iloc[:,0].to_list()
            for i in range(1,len(p)):
                all_data=all_data.append(p[i:i+1])
                all_data=all_data.append(pd.DataFrame(percent(p[i:i+1].iloc[:,1:len(col_name)+1].values / p[0:1].iloc[:,1:len(col_name)+1].values), columns=p.iloc[:,1:len(col_name)+1].columns))
                all_data=all_data.fillna('vert'+'%'+List[i])
                all_data=all_data.append(pd.DataFrame(percent(p[i:i+1].iloc[:,1:len(col_name)+1].values / p[i:i+1].iloc[:,[1,3]].values.sum()), columns=p.iloc[:,1:len(col_name)+1].columns))
                all_data=all_data.fillna('hori'+'%'+List[i])
            b=pd.DataFrame(columns=p.columns)
            b.loc[5] = 0
            b[p.columns[0]] = "p-value"
            b = b.replace(0," ")
            all_data = all_data.append(b)
        col_list=[p.columns[0]]
     
        for j in col_name:
            col_list.append(j)
        return  all_data[col_list]
    
    展开全文
  • 交叉表—用存储过程实现普通交叉表(一)开发工具:C#2008/2010数据库:Sql Server 2000/2005/2008界面:WinForm关于交叉表,各位网友用各种方法实现不少了,本文主要从用存储过程实现交叉表功能进行探讨,这种...

    交叉表—用存储过程实现的普通交叉表(一)

    开发工具:C#2008/2010

    数据库:Sql Server 2000/2005/2008

    界面:WinForm

    关于交叉表,各位网友用各种方法实现的不少了,本文主要从用存储过程实现交叉表功能进行探讨,这种方法给具体用户也做了不少。界面如下:

    867df5ec5dc51a5811832a8554d5620c.png

    一、基本数据结构

    (一)销售单主表结构

    7f0fea935aaf66199a61bc3dd077a9ac.png

    (二)销售单明细明

    8c0fd2eff0de2ed5adfbad9d76121603.png

    (三)由上述两表组成的视图

    a2b64a8cab9aa13e7d3487502a1de348.png

    二、存储过程

    --业务经理销售或兑换分品种按客户汇总表

    CREATE PROCEDURE YWYXSDHFPZKHHZ

    (

    @TJTS int,

    @StartDate VARCHAR(20),

    @EndDate VARCHAR(20),

    @YWYID VARCHAR(50),

    @KHXZ VARCHAR(50),

    @TJFW VARCHAR(50),

    @ProCate VARCHAR(50),

    @ClientCate VARCHAR(50),

    @ChanDi VARCHAR(50),

    @returnstring nvarchar(200) out

    )

    AS

    set @StartDate =@StartDate+ ' 00:00:00'--查询的开始日期

    set @EndDate =@EndDate+ ' 23:59:59'--查询的结束日期

    set @returnstring='日均销量:000'

    DECLARE @FIELD VARCHAR(50),@SQL nvarchar(2000),@SQLCondition varchar(100),@KHMC varchar(50),@chandiA varchar(50),@sl float,@FinalSL varchar(50)

    set @SQLCondition=' and 1=1'

    --业务经理条件

    if(@YWYID<>'')

    begin

    set @SQLCondition=@SQLCondition+' and ywybm='''+@YWYID+''''

    end

    --客户性质

    if(@KHXZ<>'')

    begin

    set @SQLCondition=@SQLCondition+' and kh_flfs='''+@KHXZ+''''

    end

    --产品分类

    if(@ProCate<>'')

    begin

    set @SQLCondition=@SQLCondition+' and CateID='''+@ProCate+''''

    end

    --客户分类

    if(@ClientCate<>'')

    begin

    set @SQLCondition=@SQLCondition+' and ClientCateID='''+@ClientCate+''''

    end

    --品种

    if(@ChanDi<>'')

    begin

    set @SQLCondition=@SQLCondition+' and Chandi= '''+@ChanDi+''''

    end

    --创建临时过渡表结构

    Create table #tab1

    (

    KHMC varchar(50),

    pmgg varchar(50),

    chandi varchar(50),

    sl float

    )

    --生成临时汇总表结构

    Create table #tab2

    (

    KHMC varchar(50),

    chandi varchar(50),

    sl float

    )

    if(@TJFW='')--销售和兑换数据全部统计

    begin

    --销售数据

    set @SQL='SELECT KHMC,pmgg,chandi,sum(sl) as sl FROM VIProCKDPro WHERE pzrq>='''+@StartDate+''' AND pzrq<='''+@EndDate+''' AND lbmc=''销售'' and redword=''F'' '+@SQLCondition+' GROUP BY KHMC,pmgg,chandi ORDER BY KHMC'

    insert into #tab1 EXEC sp_ExecuteSql @SQL

    --兑换数据

    set @SQL='SELECT KHMC,pmgg,chandi,sum(sl) as sl FROM VIProDHDPro WHERE pzrq>='''+@StartDate+''' AND pzrq<='''+@EndDate+''' and redword=''F'' '+@SQLCondition+' GROUP BY KHMC,pmgg,chandi ORDER BY KHMC'

    insert into #tab1 EXEC sp_ExecuteSql @SQL

    end

    else

    begin

    if(@TJFW='销售')--仅统计销售数据

    begin

    --销售数据

    set @SQL='SELECT KHMC,pmgg,chandi,sum(sl) as sl FROM VIProCKDPro WHERE pzrq>='''+@StartDate+''' AND pzrq<='''+@EndDate+''' AND lbmc=''销售'' and redword=''F'' '+@SQLCondition+' GROUP BY KHMC,pmgg,chandi ORDER BY KHMC'

    insert into #tab1 EXEC sp_ExecuteSql @SQL

    end

    else--仅统计兑换

    begin

    set @SQL='SELECT KHMC,pmgg,chandi,sum(sl) as sl FROM VIProDHDPro WHERE pzrq>='''+@StartDate+''' AND pzrq<='''+@EndDate+''' and redword=''F'' '+@SQLCondition+' GROUP BY KHMC,pmgg,chandi ORDER BY KHMC'

    insert into #tab1 EXEC sp_ExecuteSql @SQL

    end

    end

    --把品种为空的用品名替换

    update #tab1 set chandi=pmgg where chandi=''

    --再次按品种汇总,并插入临时汇总表

    insert into #tab2 select KHMC,chandi,sum(sl) as sl from #tab1 group by KHMC,chandi order by KHMC

    --创建临时表,生成给用户的交叉表结构

    Create table #tab3

    (

    客户名称 varchar(50),

    合计数量 float

    )

    --在添加字段前先用#tab2计算结果表给#tab3临时表追加数据,而且必须加distinct

    insert into #tab3 (客户名称,合计数量) select KHMC,sum(sl) as sl from #tab2 group by khmc order by KHMC

    --给#tab3增加字段

    DECLARE CUR_FIELD CURSOR LOCAL FOR SELECT distinct chandi FROM #tab2 order by chandi

    OPEN CUR_FIELD

    FETCH CUR_FIELD INTO @FIELD

    WHILE @@FETCH_STATUS=0

    BEGIN

    SELECT @FIELD='['+@FIELD+']'

    set @SQL='alter table #tab3 Add '+ @FIELD +' FLOAT not NULL DEFAULT 0 With Values'

    EXEC(@SQL)

    FETCH CUR_FIELD INTO @FIELD

    END

    --关闭游标

    CLOSE CUR_FIELD

    deallocate CUR_FIELD

    --给#tab3表的新增字段更新数据

    declare CUR_SUM cursor for select KHMC,chandi,sl from #tab2

    OPEN CUR_SUM

    FETCH CUR_SUM INTO @KHMC,@chandiA,@sl

    WHILE @@FETCH_STATUS=0

    BEGIN

    SELECT @FIELD='['+@chandiA+']',@FinalSL=CAST(@sl AS VARCHAR(50))

    SELECT @SQL='UPDATE #tab3 SET '+@FIELD+'='+@FinalSL+' WHERE [客户名称]='''+@KHMC+''''

    EXEC(@SQL)

    FETCH CUR_SUM INTO @KHMC,@chandiA,@sl

    END

    --关闭游标

    CLOSE CUR_SUM

    deallocate CUR_SUM

    declare @TotalCount float --总销量

    declare @averageCount float --日均销量

    declare @MFCount float --面粉销量

    declare @MTCount float --面条销量

    --计算日均销量

    select @TotalCount=(select round(sum(sl),2) as sl from #tab2)

    if(@TJTS=0)

    begin

    set @averageCount=@TotalCount

    END

    else

    begin

    set @averageCount=round(@TotalCount/@TJTS,2)

    END

    --计算面粉销量

    select @MFCount=(select round(sum(sl),2) as sl from #tab1 where pmgg like '%粉%' and pmgg not like '%条%')

    --计算面条销量

    select @MTCount=(select round(sum(sl),2) as sl from #tab1 where pmgg like '%面条%')

    --日均销量、面粉合计、面条合计返回变量

    set @returnstring='日均销量:'+convert(varchar(100),convert(decimal(18,2),@averageCount))+' 面粉合计数量:'+convert(varchar(100),convert(decimal(18,2),@MFCount))+' 面条合计数量:'+convert(varchar(100),convert(decimal(18,2),@MTCount))

    select * from #tab3

    --删除临时表#tab1

    drop table #tab1

    drop table #tab2

    drop table #tab3

    GO

    展开全文
  • 交叉表

    2006-07-21 12:22:00
    交叉一般来讲是分组统计的一种,形式更复杂,显示更清淅,但数据库本身并没有提供实现交叉表的功能,自己创建交叉表不仅要对过程、游标、临时表、动态SQL等非常熟悉,而且思路也要清淅,本例以PUBS.DBO.SALES表的...
    交叉一般来讲是分组统计的一种,形式更复杂,显示更清淅,但数据库本身并没有提供实现交叉表的功能,自己创建交叉表不仅要对过程、游标、临时表、动态SQL等非常熟悉,而且思路也要清淅,本例以PUBS.DBO.SALES表的数据做样本:
    CREATE PROCEDURE UP_TEST(
    @T1 VARCHAR(30),@T2 VARCHAR(30),
    @T3 VARCHAR(30),@T4 VARCHAR(30)) AS
    --T1 表名,T2,T3是交叉表的两上分类字段,T4是汇总字段
    --T2是行字段,T3列字段
    BEGIN
    DECLARE @SQL VARCHAR(7999),@FIELD VARCHAR(30)
    SELECT @SQL='SELECT DISTINCT '+@T3+' FROM '+@T1
    CREATE TABLE #FIELD(FIELD VARCHAR(30))
    --将列字段提取到临时表#FIELD中
    INSERT INTO #FIELD EXEC(@SQL)
    SELECT @SQL='CREATE TABLE CROSS_TEST('+@T2+' VARCHAR(30),'
    DECLARE CUR_FIELD CURSOR LOCAL FOR SELECT * FROM #FIELD
    OPEN CUR_FIELD
    FETCH CUR_FIELD INTO @FIELD
    WHILE @@FETCH_STATUS=0 BEGIN
    SELECT @FIELD='['+@FIELD+']'
    SELECT @SQL=@SQL+@FIELD+' DECIMAL(8,2) DEFAULT 0,'
    FETCH CUR_FIELD INTO @FIELD
    END
    SELECT @SQL=LEFT(@SQL,LEN(@SQL)-1)+')'
    --创建临时交叉表CROSS_TEST
    EXEC(@SQL)
    SELECT @SQL='INSERT INTO CROSS_TEST('+@T2+') SELECT DISTINCT '+@T2+' FROM '+@T1
    --将行数据存入交叉表#CROSS_TEST
    EXEC(@SQL)
    --创建分组数据表TEMP
    SELECT @SQL='CREATE TABLE TEMP('+@T2+' VARCHAR(30),'+@T3+' VARCHAR(30),'+@T4+' DECIMAL(8,2))'
    EXEC(@SQL)
    --将交叉汇总数据放入交叉表
    SELECT @SQL='SELECT '+@T2+','+@T3+', SUM(QTY) QTY FROM '+@T1 +' GROUP BY '+@T2+','+@T3
    INSERT INTO TEMP EXEC(@SQL)
    --将汇总数据写入交叉表
    DECLARE CUR_SUM CURSOR LOCAL FOR SELECT * FROM TEMP
    DECLARE @F1 VARCHAR(30),@F2 VARCHAR(30),@QTY DECIMAL(8,2),@Q1 VARCHAR(30)
    OPEN CUR_SUM
    FETCH CUR_SUM INTO @F1,@F2,@QTY
    WHILE @@FETCH_STATUS=0 BEGIN
    SELECT @F2='['+@F2+']',@Q1=CAST(@QTY AS VARCHAR(30))
    SELECT @SQL='UPDATE CROSS_TEST SET '+@F2+'='+@Q1+' WHERE '+@T2+'='''+@F1+''''
    EXEC(@SQL)
    FETCH CUR_SUM INTO @F1,@F2,@QTY
    END
    CLOSE CUR_SUM
    SELECT * FROM CROSS_TEST
    DROP TABLE TEMP
    DROP TABLE CROSS_TEST
    DROP TABLE #FIELD
    END
    --------------------------------------------------------
    EXEC UP_TEST 'SALES','TITLE_ID','STOR_ID','QTY'
    说明:字段加中括号为了处理字段中含有特殊字符,值得注意得是要实现交叉表的表必须有两个分类,本例只支持分类字段的数据类型是字符型的,最大的问题就是高亮显示这行的WHERE条件啦,字符类型字段查询时条件必须加单引号,如果是数值类型就可以直接写,所以数值类型的分类字段更容易实现一些,更可以融合在一个过程中。通常大家看到的交叉表都有行汇总与列汇总等信息,本例就没有实现,最后一点工作大家自己练练手吧。
     
    展开全文
  • NULLS ONGO/*--生成交叉表的简单通用存储过程根据指定的表名,纵横字段,统计字段,自动生成交叉表并可根据需要生成纵横两个方向的合计注意,横向字段数目如果大于纵向字段数目,将自动交换纵横字段如果不要此功能,...
  • 人家做平台,我做功能,太落后了,我也写点。大家不要骂,小弟脸皮薄~望大家给出建议,我项目应用范围很小。希望大家给出评论应用平台: VS2005 + SQL Server 2005 + Ajax.net... 技术点(数据库:交叉表,Ajax
  • SQL实现交叉表的方法

    2009-12-08 09:29:00
    形式更复杂,显示更清淅,但数据库本身并没有提供实现交叉表的功能,自己创建交叉表不仅要对过程、游标、临时表、动态SQL等非常熟悉,而且思路也...
  • 的功能相对完善,与微软的sharepoint服务器相结合,功能非常强大。一般来说,我们很少使用微软项目的所有功能。毕竟,与中国人的使用习惯还有很多不一致的地方。当用它来画甘特图时,我们会更频繁地使用它。如果...
  • 关于交叉表,各位网友用各种方法实现不少了,本文主要从用存储过程实现交叉表功能进行探讨,这种方法给具体用户也做了不少。界面如下: 一、基本数据结构 (一)销售单主表结构 (二)销售单明细明 ...
  • [数据库]MySQL交叉表02012-01-11 17:00:23在某些数据库中有交叉表,但在MySQL中却没有这个功能,但网上看到有不少朋友想找出一个解决方法,特发贴集思广义。...
  • 作者/排版/审核:陈明导读 SPSS软件以其操作简单,功能强大,适用人群广,备受...实际上,定制表的功能可以归纳为三类:频数分析、单选题卡方分析、多选题卡方分析,下面让我们详细了解其具体的功能。解析 定制表...
  • 在数据分析中,常常需要用到数据透视表和交叉表,下面介绍pandas.DataFrame.pivot_table数据透视表和pandas.DataFrame.crosstab交叉表的用法和区别。一、数据透视表 数据透视表用来做数据透视,可以通过一个或多个键...
  • 当时演示应该把交叉表(图1)转换成数据表格(图2)形式。 但是在excel里没有直接实现这个需求函数,Rtidyr包有一个spread可以实现这功能,但是,毕竟是R。 python同样可以实现这个需求,借助openpyxl这个包...
  • SQL交叉表

    2019-09-21 10:54:49
    之前做货品横向展示时,有看到评论说用到交叉表。 公司最近需要给订单表做一个数据汇总的功能,同事给到一个参考SQL select * from (select COUNT(1) as 已锁定 from tbl_order where orderLock = 1) as A,(select...
  • 交叉表是一种特殊透视表,往往用来统计频次,也可以使用参数aggfunc指定聚合函数实现其他功能。扩展库pandas提供了crosstab()函数用来生成交叉表,返回新DataFrame,其语法为:crosstab(index, columns, values=...
  • 生成交叉表的SQL语句

    2012-09-24 08:35:00
    /*--生成交叉表的简单通用存储过程 根据指定的表名,纵横字段,统计字段,自动生成交叉表 并可根据需要生成纵横两个方向的合计 注意,横向字段数目如果大于纵向字段数目,将自动交换纵横字段 如果不要此功能,则去掉交换...
  • 对于普通表格展示数据,相信大家都非常熟悉了...我们简单列举几个场景看看Quick BI宝藏功能——交叉表吧! 场景一:多角度排序 很多时候,当我们面对大量数据时,都需要用到排序来知晓 TOP N 等场景,在这里,...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 762
精华内容 304
关键字:

交叉表的功能