sql

今天为了统计每个用户每日推荐的数量大于3个的情况，写了如下SQL

SELECT count(*),DATE_FORMAT(RECOMMEND_TIME,'%Y-%m-%d-%H-%i-%s') s ,MEMBER_ID from DAILY_RECOMMEND
GROUP BY s DESC，MEMBER_ID
HAVING count(s)>3

思路如下：按照推荐的时间和用户ID进行分组（开始只按照推荐的时间进行分组，发现是有点问题的。如果有多个ID是同一时间推荐的呢，尽管推荐时间我是精确到了秒），再用count计算出每个分组的总数。在分组之后，使用having子句筛选出想要的分组。

 

 

知识点：

1、 出现在select后面的字段 要么是是聚合函数中的,要么就是group by 中的.

2、where和having的区别：where是先对数据进行了筛选，用group by后再对数据进行分组。having是对分组后的分组进行筛选。场景：一个用户的空间可以接受其他人的点赞。space表，like表（有点赞，和取消点赞的状态区分）。统计每个用户空间获得的赞。那么这个时候，将两个表内连接后，要先用where筛选出所有like_status=1的状态，再按userID去进行分组。

3、order by 多个字段时，用逗号分隔每一个字段，如果字段不指明排序方式，默认是增序。

排序的方法是首先按照第一个字段排序，如果第一个字段相同的，再按照第二个字段来再次排序，从而产生排序结果。

4、内连接------两个表条件限定的交集

左连接------左表的数据都会有，右表中没有符合的关联字段在左表里会填充未null

右连接------右表的数据都会有，左表中没有符合的关联字段在左表里会填充未null

全连接------是左右表的并集，他们交集重合的地方会被合并。

UNION ----会去重

UNIONALL-------只是两个表简单的合并，不会去重。所以效率较UNION 要高些

5、ifnull，不仅可以实现判断，还可以替换。isnull只能判断。

后面写这类多表的时候有发现一些问题：

1、两个表select后union，不用再select了就可以直接运行出表了。也可以再在外面select ，比如需要对某些属性进行一些计算，但这时一定要给union后的表一个名字，否则汇报错

2、对应左右连接的两个表里，有重复属性的时候，不能贸然使用 *。这样有可能会导致最后合并后，报有重复的列。目前我的解决方法是，在进行左连接的时候，select的时候只要左表的ID，在进行右连接的时候，select的时候只要右表的ID，最后连接的表就只有一个ID了。

左右连接，两个表union后

  SELECT * ,(IFNULL(un.`点赞数`,0)+IFNULL(un.`评论数`,0)*2) as '热度' from (

SELECT * from(SELECT COUNT(*) as '评论数',source_id  from community.comment_detail
WHERE check_time >"20200310" and check_status=1 and source_type=3
GROUP BY source_id
ORDER BY COUNT(*) DESC)a 
 left join
(SELECT COUNT(*) as '点赞数',source_id as 'id' from community.like_detail 
WHERE create_time >"20200310" and source_type=3
GROUP BY source_id
ORDER BY COUNT(*) DESC)b 
on a.source_id = b.id

UNION 

  SELECT * from (SELECT COUNT(*) as '评论数',source_id from cms.`comment` 
WHERE check_time >"20200310" and check_status=1 and source_type=3
GROUP BY source_id 
ORDER BY COUNT(*) DESC)c
 RIGHT JOIN 
(SELECT COUNT(*) as '点赞数',source_id as 'id' from cms.like_detail 
WHERE create_time >"20200310" and source_type=3
GROUP BY source_id 
ORDER BY COUNT(*) DESC)d 
on c.source_id = d.id
)un

ORDER BY  (IFNULL(un.`点赞数`,0)+IFNULL(un.`评论数`,0)*2) DESC

今天测试了一个需求有一个点是计算每个用户近7天所有动态的热度，不同天发的热度按到计天数的热度因子不一样

查询思路：先过滤出进7天的动态，然后按每个用户ID和发动态日期进行分组，统计出每个用户每天所有动态的热度，然后根据分组的时间，对所有用户不同天的热度进行转换，最后按用户ID分组统计每个用户近7天的热度总和。

统计会员近7天动态的热度总和，每天的动态热度随着时间过去热度递减

思路：先按会员ID和日期进行分组，统计出每个会员每天的动态热度。再按热度计算公式每个用户近7天的热度（其他天就默认热度为0,）最后按用户ID进行分组，累加每个用户近7天的热度。