精华内容
下载资源
问答
  • 【MySQL】04-分组查询

    2020-04-10 09:21:00
    一、分组查询 特点: 案例1 1.添加筛选条件 2.分组后筛选 根据count个数进行筛选 3.分组前后筛选条件 4.按照分组函数分组 5.按照多个字段分组 6.添加排序 order by ...

    一、分组查询

    在这里插入图片描述

    特点:

    在这里插入图片描述

    案例1

    在这里插入图片描述

    1.添加筛选条件

    在这里插入图片描述

    2.分组后筛选 根据count个数进行筛选

    在这里插入图片描述

    3.分组前后筛选条件

    在这里插入图片描述

    4.按照分组函数分组

    在这里插入图片描述

    5.按照多个字段分组

    在这里插入图片描述

    6.添加排序 order by

    在这里插入图片描述

    展开全文
  • 4 根据条件筛选数据 5 分组计数 6 按某列分组后,对其它列进行统计 7 横向连接表 8 纵向连接表 9 分组后排序 10 按条件分组 11 按条件更新某个值 12 按条件删除行 13 删除列 14 提取/匹配字符串 15 替换...

    目录

    1 查询前N行

    2 查询特定列

    3 查询某列的去重数据

    4 根据条件筛选数据

    5 分组计数

    6 按某列分组后,对其它列进行统计

    7 横向连接表

    8 纵向连接表

    9 分组后排序

    10 按条件分组

    11 按条件更新某个值

    12 按条件删除行

    13 删除列

    14 提取/匹配字符串

    15 替换字符串中字符

    16 根据某列,对另外一列进行计数


    1 查询前N行

    # python
    order_data.head(10)
    
    # mysql
    select * from t_order limit 10

    2 查询特定列

    # python
    # 1
    order_data[['orderid', 'amount']]
    # 2
    order_data.loc[:,['orderid', 'amount']]
    # 3
    order_data.iloc[:,[3,4]]
    
    
    # mysql
    select orderid,amount from t_order

    3 查询某列的去重数据

    # python
    # 查询所有不重复的值
    order_data['uid'].unique()
    # 查询所有不重复值的数量
    order_data['uid'].nunique()
    
    # mysql
    # 查询所有不重复的值
    select distinct uid from t_order
    # 查询所有不重复值的数量
    select count(distinct uid) from t_order

    4 根据条件筛选数据

    # python
    # 且用 &, 或用 |
    order_data[(order_data['uid'] == 10003) & (order_data['amount'] > 50)]
    
    
    # mysql
    # 且用 and, 或用 or
    select * from t_order
    where uid = 10003
    and amount > 50

    5 分组计数

    # python
    order_data.groupby('uid').size()
    
    # mysql
    select uid,count(*) from t_order
    group by uid

    6 按某列分组后,对其它列进行统计

    # 按uid分组后,分别对amount和orderid列进行统计
    
    # python
    # agg支持的fun名字:count、sum、mean、median、min、max、prod(求积)、std(标准差)、var(方差)、
    # unique(罗列不重复的项)、first、last(最后一个非NA值)
    # 复杂的可以使用apply函数
    order_data.groupby('uid').agg({'amount':['mean','sum'],'orderid':['count']})
    
    # mysql
    # 取两位小数round(sum(amount),2)
    select uid, sum(amount) as amount_mean, count(orderid) as orderid_count 
    from t_order
    group by uid

    7 横向连接表

    # python
    # how的参数有left/right/outer/inner
    pd.merge(order_data, user_data, on='uid', how='left')
    
    # mysql
    # left join/right join/outer join/inner join
    select *
    from t_order
    left join t_user
    on t_order.uid = t_user.uid

    8 纵向连接表

    # python
    # 不去重
    pd.concat([order_data,order2_data])
    # 去重
    pd.concat([order_data,order2_data]).drop_duplicates()
    
    # mysql
    # 不去重
    select * from t_order
    union all
    select * from t_order2
    # 去重
    select * from t_order
    union
    select * from t_order2

    9 分组后排序

    # python
    # 降序
    order_data.groupby('uid').size().sort_values(ascending=False)
    # 多列排序
    order_df= order_data.groupby('uid').agg({'orderid':'count','amount':'sum'})
    order_df.rename(columns={'orderid':'orderid_count','amount':'amount_sum'},inplace=True)
    order_df.sort_values(by=['orderid_count', 'amount_sum'], ascending=[False, True])
    
    # mysql
    # 降序
    select uid, count(*) as uid_count
    from t_order
    group by uid
    order by uid_count desc
    # 多列排序
    select uid, count(orderid) as orderid_count, sum(amount) as amount_sum
    from t_order
    group by uid
    order by orderid_count desc, amount_sum

    10 按条件分组

    # 按uid字段对总金额求和后,再把总金额分为[0-300),[300,600),[600,900)三组
    # python
    def func(x):
        if x < 300:
            return '[0,300)'
        elif x < 600:
            return '[300,600)'
        elif x < 900:
            return '[600,900)'
        else:
            return 'other'
    
    order_df= order_data.groupby('uid').agg({'orderid':'count','amount':'sum'})
    order_df.rename(columns={'orderid':'orderid_count','amount':'amount_sum'},inplace=True)
    order_df.sort_values(by=['orderid_count', 'amount_sum'], ascending=[False, True])
    order_df['amount_inerval'] = order_df['amount_sum'].map(func)
    
    # mysql
    select uid, orderid_count, 
    case when amount_sum < 300 then '[0,300)'
    		 when amount_sum >= 300 and amount_sum < 600 then '[300,600)'
    		 when amount_sum >= 600 and amount_sum < 900 then '[600,900)'
    else 'other' end as amount_inerval
    from(
    select uid, count(distinct orderid) as orderid_count, sum(amount) as amount_sum
    from t_order
    group by uid
    ) A

    11 按条件更新某个值

    # 20岁及以下的年龄替换成20岁
    # python
    user_data.loc[user_data['age']<=20,'age'] = 20
    
    # mysql
    # 先选中表
    select * from t_user;
    update t_user set age = 20 where age <=20
    from t_user

    12 按条件删除行

    # 删除20岁及以下的
    # python
    user_data = user_data[user_data['age'] > 20]
    
    # mysql
    delete from t_user where age <= 20

    13 删除列

    # python
    user_data.drop(['uid'], inplace=True, axis=1)
    
    # mysql
    # alter table 删除列drop,添加列add
    alter table t_user drop column uid

    14 提取/匹配字符串

    # Python
    # 提取前8位
    order['orderid'].astype(str).str[:8]
    # 模糊匹配后提取,返回的是正则表达式中() 对应的结果
    order['ts'].astype(str).str.extract('(\d{4}-\d{2}-\d{2}).*')
    # 模糊匹配后筛选,返回的是布尔值
    order['ts'].astype(str).str.contains('08-01')
    
    # Hive SQL
    # 提取前8位
    # substr(string A, int start, int len)
    select *, substring(orderid, 1, 8) as dt
    from t_order;
    # 模糊匹配后提取
    # regexp_extract(str, regexp[, idx])
    # idx默认值是1,表示返回正则表达式中第一个() 对应的结果。以此类推。0表示返回全部结果
    select *, regexp_extract(ts, '(\\d{4}-\\d{2}-\\d{2}).*', 1) as dt
    from t_order;

    15 替换字符串中字符

    # 将“-”替换成空
    # Python
    order[order['ts'].astype(str).str.replace("-","")]
    
    # Hive SQL
    select *, regexp_replace(ts, '-', '') as dt
    from t_order;

    16 根据某列,对另外一列进行计数

    # 统计ts字段中含有‘2019-08-01’的订单有多少,ts字段中含有‘2019-08-02’的订单有多少
    
    # Python
    # DataFrame.apply(self, func, axis=0, raw=False, result_type=None, args=(), **kwds)
    # axis {0 or ‘index’: apply function to each column. 
    # 1 or ‘columns’: apply function to each row.}, default 0
    def func(x):
        if '2019-08-01' in x['ts']:
            return '2019-08-01'
        elif '2019-08-02' in x['ts']:
            return '2019-08-02'
        else:
            return None
    order['count_condition'] = order.apply(func, axis=1)
    order.groupby('count_condition').agg({'orderid': 'count'})
    
    # Hive SQL
    # case when ts like '%08-01%' then orderid end
    # 如果ts列包含“08-01”则替换为orderid列的内容,如果不包含则为空
    select count(case when ts like '%08-01%' then orderid end) as 0801_count, 
    count(case when ts like '%08-02%' then orderid end) as 0802_count
    from t_order;

     

    展开全文
  • 根据 A,B 两个字段进行分组,查询出重复的记录,并显示重复记录(不包括A最小的记录)。 如果需要完整显示重复记录,将WHERE条件中的第二行去除即可。 ( SELECT ID FROM Table1 AS t1 WHER...

    最前

    由于近期在搞一些爬虫,手艺不佳经常挂掉,于是重启之后往往在数据库形成了重复的记录。这里介绍一种方法,试图根据两个或多个字段值去除重复的记录。

    查询重复记录

    根据 A,B 两个字段进行分组,查询出重复的记录,并显示重复记录(不包括A最小的记录)。

    如果需要完整显示重复记录,将WHERE条件中的第二行去除即可。

    ( SELECT
    	ID 
    	FROM
    		Table1 AS t1 
    	WHERE
    	( t1.A, t1.B ) IN ( SELECT A, B FROM Table1 GROUP BY A, B HAVING count(*) > 1 ) 
    	AND t1.A NOT IN ( SELECT min( A ) FROM Table1 GROUP BY A, B HAVING count(*) > 1 )
    )
    

    删除重复记录

    针对 MSSQL、PostgreSQL 等数据库

    利用上述查询的结果,直接利用IN的方法删除重复记录即可,仅保留A最小的记录。

    DELETE 
    FROM
    	Table1 
    WHERE
    	ID IN (
    	SELECT
    		ID 
    	FROM
    		Table1 AS t1 
    	WHERE
    	( t1.A, t1.B ) IN ( SELECT A, B FROM Table1 GROUP BY A, B HAVING count(*) > 1 ) 
    	AND t1.A NOT IN ( SELECT min( A ) FROM Table1 GROUP BY A, B HAVING count(*) > 1 )
        )
    

    针对 MySQL 数据库

    有趣的是,当我在 MySQL 数据库中运行上述命令时,会提示如下错误:

    1093 - You can’t specify target table ‘Tabel1’ for update in FROM clause

    意思是,在 MySQL 数据库中,无法UPDATE在本条查询语句中已经SELECT过的语句,鉴于我是单表查询,所以难以回避这个问题。我的解决方案是使用CREATE TEMPORARY TABLE temp创建临时表,再对临时表进行查询删除操作。

    1. 查询新建临时表

      CREATE TEMPORARY TABLE temp (
      	SELECT
      		ID 
      	FROM
      		Table1 AS t1 
      	WHERE
      	( t1.A, t1.B ) IN ( SELECT A, B FROM Table1 GROUP BY A, B HAVING count(*) > 1 ) 
      	AND t1.A NOT IN ( SELECT min( A ) FROM Table1 GROUP BY A, B HAVING count(*) > 1 )
      	)
      
    2. 根据临时表进行查询删除

    DELETE 
    FROM
    	Table1 
    WHERE
    	A IN ( SELECT A FROM temp )
    

    完成查询重复记录,并删除!一般来说,临时表会在关闭数据库连接时自动删除,也可以通过DROP TABLE temp命令手动删除。

    展开全文
  • 高级(ad-hoc)查询,即时数据分析,计算字段,10评级,根据季度将数据分组,设置解决方案的格式,常见的故障排除方法,外部和OLAP数据,数据透视图,利用VBA实现自动化,Excel服务器,熟悉功能区。  “使用没有...
  • 添加分组操作,增加分组消息提示 2008-10-26增加系统信息,上下栏,初步集成多用户博客平台 性能测试,结果显示好友人数200上限,还凑合 人数再多的话,由于10秒刷新好友列表,请求得不到即时响应,使用起来会不...
  • ____强大的通信录管理,支持批量导入联系人信息、按条件搜索联系人信息、名片夹一键备份恢复等; ____在短信正文,自动加入接收者合适的称呼(群发时尤为有用); ____可发送即时短信、定时短信、循环短信;实时...
  • 9.7 分组查询,统计函数(GROUP BY) 181 9.7.1 统计函数 182 9.7.2 统计函数GROUP_CONCAT() 183 9.7.3 对多个数据列进行GROUP BY查询 184 9.7.4 GROUP BY...WITH ROLLUP 184 9.8 修改数据(INSERT、UPDATE和...
  • 2.3 条件语句 实例028 判断某一年是否为闰年 实例029 验证登录信息的合法性 实例030 为新员工分配部门 实例031 用Switch语句根据消费金额计算折扣 实例032 判断用户输入月份的季节 2.4 循环控制 实例033 ...
  • 思路:自连接两次,关联条件为时间点和时间点加一及时间点减一关联,取金额大于一时刻且大于后一时刻的作为波峰 2.拉链表的设计 思路:增加主键分组排序序号列,根据序号和序号加一自关联,关联不上的闭链 3....

    1.找出股票曲线图的所有波峰和波谷

    思路:自连接两次,关联条件为时间点和时间点加一及时间点减一关联,取金额大于前一时刻且大于后一时刻的作为波峰

    2.拉链表的设计

    思路:增加主键分组排序序号列,根据序号和序号加一自关联,关联不上的闭链

     

    3.行转列,数据拉平

    思路:按主键分组,指标列用case when做聚合

     

    4.列转行和行转列

    TABLE_A

    student chinese maths english
    S01 79 81 93
    S02 67 75 71

     

     

     

     

    SELECT
        Student,object,split(object,',')[0] as kemu,split(object,',')[1] as score
    FROM TABLE_A
    lateral view explode (array(concat('chinese,',chinese),concat('maths,',maths),concat('english,',english))) mm as object

    TABLE_B

    student object kemu score
    S01 chinese,79 chinese 79
    S01 maths,81 maths 81
    S01 english,93 english 93
    S02 chinese,67 chinese 67
    S02 maths,75 maths 75
    S02 english,71 english 71

     

     

     

     

     

     

     

     

    注意上面的array(concat('chinese,',chinese),concat('maths,',maths),concat('english,',english))

    实际的返回值是一个数组:['chinese,79','maths,81','english,93']

     

    方法一:

    select student
    ,max(case when kemu = 'chinese' then score else 0 end) as chinese
    ,max(case when kemu = 'chinese' then score else 0 end) as chinese
    ,max(case when kemu = 'chinese' then score else 0 end) as chinese
    from TABLE_B
    group by student

    方法二:

    select student
        ,concat_ws('',collect_set(chinese)) as chinese
        ,concat_ws('',collect_set(maths)) as maths
        ,concat_ws('',collect_set(english)) as english
    from (
            select student
            ,case when kemu = 'chinese' then score else ‘’ end as chinese
            ,case when kemu = 'maths' then score else ‘’ end as maths
            ,case when kemu = 'english' then score else ‘’ end as english
            from TABLE_B
         ) a
    group by student
    注意上面的collect_set的返回结果是对字段的值去重之后的一个数组(collect_list是不去重),由于上面的case when的else是'',所以按照student分组后collect_set得到的数组只有一个元素,反之如果else的是0就会造成数组有两个值['79','0'],后续就会有问题,concat_ws是把他里面的参数合并返回成一个string类型的结果(其中第一个参数是合并的分隔符,这边是不需要分隔符所以用的是''),再次强调concat_ws即是里面的参数是数组也是最终返回string类型,例:concat(':',array('a','b'),'c')返回值是a:b:c

    5.客户的某一个月的游戏充值情况如下,请帮忙给核心客户打上标签(客户充值金额从大到小排序,构成总金额的70%以上的作为核心客户)

    TABLE_A

    CUST_NO MONEY
    A01 700
    A01 800
    A01 900
    A02 300
    A02 120
    A02 100
    A02 200
    A02 400
    A02 500

     

     

     

     

     

     

     

     

     

     

    思路:需要取到的几个要素1全部客户总金额2各客户的金额

    取到两个要素后按照要素2从大到小排序累加,当这个累加金额>要素1的80%是,取到的这部分客户是核心客户

    具体可以拆成两步,

    步骤一:得到客户号,元素1,元素2

    select    cust_no
    ,sum(sum(money)) over(partition by 1)        as money_all    --取全部总金额
    ,sum(sum(money)) over(partition by cust_no)    as cust_money    --取客户的金额
    from table
    group by cust_no

    *注意groupby的sum的括号是加在哪个地方

    --TABLE_B

    cust_no money_all cust_money
    A01 4020 2400
    A02 4020 1620

     

     

     

     

    select    cust_no
    ,sum(cust_money) over(order by cust_money asc) as cust_money    --排序
    ,case when sum(cust_money) over(order by cust_money ASC)*100/money_all >= 20 then 'Y' ELSE 'N' END    AS IS_HX
    from table

    *注意此处为何用从小排到大取>=0.2的原因是因为若是从大排到先去<=0.8的话会漏掉那个分界线的客户

     

    6.某区域包含n家门店,每家没点销售的商品不同,如何找出每样商品销售数量均大于30的门店

    思路:一家门店的所有产品的销售量的最小值大于30

    select 门店

    from 表

    group by 门店

    having min销售数 大于30

     

    7.count(*),count(1)和count(column)区别

    前两者是返回数据表的行数,后者是返回改字段中不为null值的数据行数

     

    8.排序的区别

    row_number()顺序排序(不允许并列),即使中间数值相同也会顺序向下排序,序号不断;

    rank()跳档排序,中间有数值相同会跳档排序,有两个第三名时,此种排序不出现第四名,顺位第五向下

    dense_rank()顺序排序(允许并列情况)中间数值相同的情况允许并列,可以有并列第三名,且下一个顺位是排为第四名

    9.

    一个表中只有a,b,c,d4支球队,求所有可能的对战情况

    思路:笛卡尔积的1/2

    select a.name, b.name 

    from team a, team b 

    where a.name < b.name

    10.几个高效的窗口函数

    查询客户上次和下次的购买时间

    Lag() over ;lead() over

    select name,orderdate,cost, lag(orderdate,1,'无上条订单') over(partition by name order by orderdate) prevdate,

     lead(orderdate,1,'无下条订单') over(partition by name order by orderdate) behinddate

    from business

    查询前20%时间的订单信息

    ntile() over

    select name,orderdate,cost, ntile(5) over(order by orderdate) sorted  from business

     

     

    irst_value(col)

    select name,orderdate,cost,first_value(col) over(partition by name,month(orderdate) order by orderdate )

    from business

    --根据name,month(orderdate)分组,orderdate排序取最早的orderdate那条数据对应的col字段的值

     

     

     

    展开全文
  • 用户可以根据自己的需求添加并命名分组分组设定好之后,用户可以选择不同的好友添加到相应的分组中。//固定分组 (4)好友管理模块: 用户可以根据自己的需求添加、删除好友,也可以对好友的备注进行修改。另外...
  • T-SQL高级查询

    2015-04-26 14:17:13
    按照年龄分组,过滤条件分组后的记录条数大于等于2 select count(*), age from student group by age having count(age) >= 2; 按照cid和性别组合分组,过滤条件是cid大于1,cid的最大值大于2 select count(*), ...
  • having 子句的作用是筛选满足条件的组,即在分组之后过滤数据,条件中经常包含聚组函数,使用having 条件显示特定的组,也可以使用多个分组标准进行分组。 having 子句被限制子已经在SELECT语句中定义的列和聚合...
  • 根据给定的条件,对数据进行合计 十列数据合计成一列 统计汉字字符个数 关于取数 统计单元格内不为空的格数 自动将销量十名的产品代号及销量填入表中 统计最大的连续次数3个“不重复”个数统计在一列有重复的姓名...
  • 6.3.16 Aggregate 根据输入的表达式获取一个聚合值 24 6.3.17 DefaultIfEmpty 查询结果为空则返回默认值,浅复本 24 6.3.18 ToArray 将集合转换为数组,浅复本 24 6.3.19 ToList 将集合转换为List集合,浅复本 25 ...
  • 例5、写出数列的一个通项公式,使它的前5项分别是下列各数:  (1)  (2) ; (3)5,55,555,5555,55555. 解析:  (1) (2) (3) 小结:  根据数列的前n项写通项公式,一般围绕2n,2n±1,n2,n2±1,2n,...
  • 问题5-12:糊涂窗口综合症产生的条件是什么?是否只有在接收方才产生这种症状? 问题5-13:能否更详细些讨论一下糊涂窗口综合症及其解决方法? 问题5-14:为什么TCP在建立连接时不能每次都选择相同的、固定的初始...
  • 5章 程序控制应用 5.1 程序控制 实例107 打开控制面板中的程序 实例108 动态系统托盘图标 实例109 控制程序不出现在任务栏上 实例110 启动屏幕保护 实例111 使用回车键控制鼠标焦点 实例112 在textbox控件中禁用...
  • 为了方便阅读,本人在上传添加了完整的书签。 内容简介  《ASP.NET开发实战1200例》分为I、II两卷共计1200个例子,包括了开发中各个方面最常用的实例,是目前市场上实例最全面的开发类图书;书中实例来源于多位...
  • 5章 程序控制应用 5.1 程序控制 实例107 打开控制面板中的程序 实例108 动态系统托盘图标 实例109 控制程序不出现在任务栏上 实例110 启动屏幕保护 实例111 使用回车键控制鼠标焦点 实例112 在textbox控件中禁用...
  • 为了方便阅读,本人在上传添加了完整的书签。 内容简介  《ASP.NET开发实战1200例》分为I、II两卷共计1200个例子,包括了开发中各个方面最常用的实例,是目前市场上实例最全面的开发类图书;书中实例来源于多位...
  • EXCEL函数公式集

    热门讨论 2010-03-16 03:26:38
    根据给定的条件,对数据进行合计 十列数据合计成一列 统计汉字字符个数 关于取数 统计单元格内不为空的格数 自动将销量十名的产品代号及销量填入表中 统计最大的连续次数3个“不重复”个数统计在一列有重复的姓名...
  • 5章 程序控制应用 5.1 程序控制 实例107 打开控制面板中的程序 实例108 动态系统托盘图标 实例109 控制程序不出现在任务栏上 实例110 启动屏幕保护 实例111 使用回车键控制鼠标焦点 实例112 在textbox控件中禁用...
  • 21天学会SQL

    2013-04-10 14:46:04
    9.4.5 查询前5行数据 189 9.4.6 WHERE与ORDER BY的 结合使用 189 9.5 高级条件查询 190 9.5.1 AND运算符 190 9.5.2 OR运算符 191 9.5.3 AND与OR的优先顺序问题 192 9.5.4 NOT运算符 193 9.5.5 IN运算符 193 9.5.6 ...
  • 027 将三个部门的员工随机分组 77 028 随机抽取5名员工的编号 81 029 随机抽取一名员工工资数据 84 ◎取舍函数 87 030 将跳远平均成绩保留两位小数 87 031 以“万元”为单位计算当月的产品销售总利润 89 ...
  • 问题1-5:在运输层应根据什么原则来确定应当使用面向连接服务还是无连接服务? 问题1-6:在数据链路层应根据什么原则来确定应当使用面向连接服务还是无连接服务? 问题1-7:TCP/IP的体系结构到底是四层还是五层? ...
  • 第三个问题,即使现在点击第二页按钮时,可以返回第6-10条数据,但根据下面的摄sql语句,由于是用id进行分页的,而id是没有重复的,查询操作会直接前5条记录去除,从数据库的第6条开始查询满足条件的记录,而这必然...

空空如也

空空如也

1 2 3 4 5 ... 9
收藏数 175
精华内容 70
关键字:

根据条件分组前5