MySQL社区

 找回密码
 注册

QQ登录

只需一步,快速开始

扫一扫,访问微社区

搜索
查看: 3876|回复: 5

[SQL类] 使用 GROUP BY 语句 将数据表按shop_id进行分组,并统计每天有多少条记录:

[复制链接]
发表于 2017-2-8 10:16:31 | 显示全部楼层 |阅读模式
表结构:
  1. CREATE TABLE `user_view` (
  2.   `user_id` varchar(255) default NULL,
  3.   `shop_id` varchar(255) default NULL,
  4.   `time_stamp` varchar(255) default NULL
  5. ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
复制代码
表内容:
  1. 13201967        1197        2016-10-21 18:00:00
  2. 19461365        1197        2016-06-28 23:00:00
  3. 15022321        1197        2016-07-16 19:00:00
  4. 5440872        1197        2016-07-15 07:00:00
  5. 12594529        1197        2016-08-07 16:00:00
  6. 5440872        1197        2016-08-12 08:00:00
  7. 19918044        1197        2016-09-03 16:00:00
  8. 9476039        1197        2016-09-04 17:00:00
  9. 4081995        1197        2016-09-25 10:00:00
  10. 12594529        1197        2016-08-07 16:00:00
  11. 3367000        1197        2016-10-07 19:00:00
  12. 7544345        1197        2016-09-03 22:00:00
  13. 16877799        1197        2016-09-04 21:00:00
  14. 4081995        1197        2016-09-25 10:00:00
  15. 13901224        1197        2016-09-07 17:00:00
  16. 12436010        1197        2016-08-31 13:00:00
  17. 11417229        1197        2016-08-06 07:00:00
  18. 。。。。
复制代码
这张表是 2000家 线上商店  在 2016-06-22 00:00:00至2016-10-31 23:00:00 用户的浏览记录我想 根据 shop_id 进行分组,再统计 每一家商店 每天的访问量




test.txt

2.39 MB, 下载次数: 165

部分数据集

 楼主| 发表于 2017-2-8 10:42:33 | 显示全部楼层
我使用的是navicat for mysql  操作的
下图是 部分数据集截图

部分数据集截图

部分数据集截图
使用 GROUP BY 语句 将数据表按商店名字(shop_id)进行分组,并统计每个shop_id有多少条记录:

  1. SELECT
  2. study.shop_id, COUNT(*)
  3. FROM
  4. study
  5. GROUP BY study.shop_id;
复制代码


操作与sql语句

操作与sql语句


使用 WITH ROLLUP
WITH ROLLUP 可以实现在分组统计数据基础上再进行相同的统计(SUM,AVG,COUNT…)。
我们将以上的数据表按shop_id进行分组,再统计每天访问的次数:
  1. SELECT study.shop_id,study.time_stamp, SUM(study.time_stamp) as view_count FROM  study GROUP BY study.shop_id WITH ROLLUP;
复制代码
1.png
可以看到操作是错误的。。。。
想办法把时间点 time_stamp 统计成每天的


 楼主| 发表于 2017-2-8 11:21:50 | 显示全部楼层
按照CSDN的一个大哥的回答:
  1. select study.shop_id,left(study.time_stamp,10),count(*)
  2. from study
  3. group by  study.shop_id,left(study.time_stamp,10)
复制代码


1.png

请教下left在这里使用的含义是什么?
 楼主| 发表于 2017-2-8 15:24:44 | 显示全部楼层
现在获取到了每天,每个商家的客户流量 ,如下:
表一:user_pay_count:商家每天客户流量数据
  1. CREATE TABLE `user_pay_count` (
  2.   `shop_id` varchar(255) default NULL,
  3.   `time_stamp` varchar(255) default NULL,
  4.   `count` varchar(255) default NULL
  5. ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
复制代码

1.png

现在想把上表和另一张表二合并成一张新表
表二:shop_info:商家特征数据
  1. CREATE TABLE `shop_info` (
  2.   `shop_id` varchar(255) default NULL,
  3.   `city_name` varchar(255) default NULL,
  4.   `location_id` varchar(255) default NULL,
  5.   `per_pay` varchar(255) default NULL,
  6.   `score` varchar(255) default NULL,
  7.   `comment_cnt` varchar(255) default NULL,
  8.   `shop_level` varchar(255) default NULL,
  9.   `cate_1_name` varchar(255) default NULL,
  10.   `cate_2_name` varchar(255) default NULL,
  11.   `cate_3_name` varchar(255) default NULL
  12. ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
复制代码
2.png

现在怎么把表一的time_stamp这一列每天的日期,变成表二里的列,
把表一与表二的shop_id相对应,把每天的客户流量  count  记录在
新表里,我的构想是如下图:

3.png

请教下该怎么写SQL语句啊?




2.png
 楼主| 发表于 2017-2-8 16:27:24 | 显示全部楼层
  1. SELECT
  2. shop_info.shop_id,
  3. user_pay_count.shop_id,
  4. shop_info.city_name,
  5. shop_info.location_id,
  6. shop_info.per_pay,
  7. shop_info.score,
  8. shop_info.comment_cnt,
  9. shop_info.shop_level,
  10. shop_info.cate_1_name,
  11. shop_info.cate_2_name,
  12. shop_info.cate_3_name,
  13. user_pay_count.time_stamp,
  14. user_pay_count.count
  15. FROM
  16. shop_info ,
  17. user_pay_count
  18. WHERE shop_info.shop_id=user_pay_count.shop_id
复制代码

1.png

现在这个结果不是我想要的,我想要把时间这列 转换成列名
每行数据为1个商家,从2015-06-26 至2016-10-31每天的客户流量
一共2000个商家,2000行这样的表结构

转变下了思路,保持表shop_info结构不变,然后把表shop_info表里的10列数据直接插入到

表user_pay_count里,变成下图:
3.png

所以现在想把表user_pay_count里的时间列里的每天时间点  变成  列名
 楼主| 发表于 2017-2-8 16:46:45 | 显示全部楼层
【列转行】把时间列里的每一天(2015-10-10 ),转成新的列名现在表的数据如下图:
1.png
想把上图的每一个时间 2015-10-10 、2015-10-11、2015-10-12、。。。。 变成列名
按照shop_id分组,每行一个shop_id
  1. SELECT
  2. user_pay_count.shop_id,
  3. MAX(CASE user_pay_count.time_stamp WHEN '2016-10-30' THEN user_pay_count.count ELSE 0 END) 2016-10-30
  4. MAX(CASE user_pay_count.time_stamp WHEN '2016-10-31' THEN user_pay_count.count ELSE 0 END) 2016-10-31
  5. FROM
  6. user_pay_count
  7. GROUP BY user_pay_count.shop_id
复制代码
  1. [Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '2016-10-30
  2. MAX(CASE user_pay_count.time_stamp WHEN '2016-10-31' THEN user_pay_c' at line 3
复制代码
请问该怎么写,自动执行,生成从2015-06-26 至2016-10-31 这 493天,493列数据:
2.png

您需要登录后才可以回帖 登录 | 注册

本版积分规则

QQ|申请友链|小黑屋|Archiver|手机版|MySQL社区 ( 京ICP备07012489号   
联系人:周生; 联系电话:13911732319

GMT+8, 2024-3-29 16:26 , Processed in 0.072596 second(s), 26 queries , Gzip On.

Powered by Discuz! X3.2

© 2001-2013 Comsenz Inc.

快速回复 返回顶部 返回列表