从数据看周末，sql统计的简单实践

今天一早（2023年9月16日）查看个人站的RSS新闻信息，还没看几页内容就没了，赶紧看看是不是定时任务出问题了，看来一遍发现一切正常。突然想到今天是周六，想着是不是周六了都休息了，所以新闻内容少了赶紧打开数据库查一下。因为服务没上线几天，所以统计数据不多，但是刚好包含上个周末的信息。

先贴出统计的sql select count(id), date(created_at), date_format(date(created_at), "%W") from items group by date(created_at); ,以天为聚类条件，并使用了 date_fromat()显示星期几，下面是结果。

其中count(id) 是当天的数据量，可以很清晰的看到，在周末的时候数据量相较于平时少了大概1000条左右。这足以说明，双休还是存在的。

可是，仅仅这些就够了吗？为什么我没感觉到周末的快乐呢？所以进一步，统计哪些网站的平时数据和周末数据差距不大，或者统计哪些网站的差距很大。说干就干。

首先要知道统计什么数据来看出差距，不懂啊。问claude

1. 四分位距/五数概括:计算出数据集的最小值、最大值、中位数、上四分位数和下四分位数,可以直观反映出数据的分布范围和集中趋势。
2. 方差和标准差:反映数据相对于平均值的离散程度,标准差越大表示数据越分散。
3. 偏度和峰度:偏度反映数据分布的对称性,正偏表示右偏分布;峰度反映分布的尖峭程度,峰态分布峰值越尖。
4. 直方图和密度图:直方图反映不同取值范围内的数据频数,密度图显示密度曲线,可以直接观察分布形状。
5. QQ图:通过与正态分布的分位数进行比较,判断数据是否遵循正态分布

还是懵，继续问。

如果要统计一个网站每天的信息数量,并判断一段时间内每天信息量的差异大小,可以考虑使用以下统计方法:

1. 汇总统计:统计出每个时间段(例如每天)的信息数量,直接看每天的具体数值大小。
2. 平均数和标准差:计算出信息量的平均值和标准差,通过标准差可以判断信息量的日浮动情况。标准差越大表示信息量波动越大。
3. 方差分析:对不同时间段的信息量进行方差分析,看信息量在不同时间之间是否存在显著性差异。如果p值很小,则说明不同时间的信息量差异显著。
4. 时间序列分析:采用时间序列图显示信息量随时间的变化趋势,直接观察信息量是否存在周期性规律或明显的上升/下降趋势。
5. 数据拟合:可以试着对信息量进行曲线拟合,看是否符合某种分布规律。如符合正态分布则差异不大,若不符合则存在较大波动。

综合这些统计方法,可以很好地判断网站信息量的大小分布和时间间差异情况。实际应用时,可以根据数据特点选择最适合的方法。

有了，就用标准差计算。标准差越大表示信息量波动越大。标准差怎么计算呢？再问！

标准差的计算方法如下:

假设有一个样本数据集:X1, X2, ..., Xn

则该样本的均值是:

μ = (X1 + X2 + ... + Xn) / n

该样本的方差是:

σ2 = ∑(Xi - μ)2 / (n - 1)

即对所有样本数据与均值的差的平方进行求和,然后除以样本数n减1。

该样本的标准差是方差的算术平方根:

σ = √σ2

举例:

假设样本数据为:3, 5, 4, 4, 5, 2

则, 均值 μ = (3 + 5 + 4 + 4 + 5 + 2) / 6 = 4

方差 σ2 = [(3-4)2 + (5-4)2 + (4-4)2 + (4-4)2 + (5-4)2 + (2-4)2] / (6 - 1) = 5/5 = 1

标准差 σ = √1 = 1

所以该样本数据集的标准差为1。

简化一下，用人话来说就是，先计算数据的平均值，再用平均值计算方差，然后再对方差平方根，就是标准差。

开干，以公司和日期为聚类条件sql select count(items.id), date(items.created_at), date_format(date(items.created_at), "%W"), rss_links.name from items inner join rss_links on items.rss_links_id=rss_links.id group by date(created_at), rss_links_id;，将这个查询作为子查询，在这个查询的基础上聚类统计每个网站多天的平均值。

每个网站平均每天的消息

select avg(temp_table.c) as a, temp_table.n  from (SELECT count(items.id) as c, date(items.created_at) as d, date_format(date(items.created_at), "%W") as w, rss_links.name as n
FROM items 
INNER JOIN rss_links ON items.rss_links_id=rss_links.id
GROUP BY date(items.created_at), date_format(date(items.created_at), "%W"), rss_links.name) as temp_table GROUP BY temp_table.n

部分结果

得到了平均数，在计算平方差，对sql稍作修改，

select sum((c-a1)*(c-a1))/(count(d)-1) as fc, n 
from (
	select avg(temp_table.c) as a1, temp_table.n as n1 
			from (
				SELECT count(items.id) as c, date(items.created_at) as d, rss_links.name as n
					from items INNER JOIN rss_links ON items.rss_links_id=rss_links.id
						GROUP BY date(items.created_at), rss_links.name
					) as temp_table GROUP BY temp_table.n
		) as t1 
inner join (
	select count(items.id) as c, date(items.created_at) as d, rss_links.name as n
					from items INNER JOIN rss_links ON items.rss_links_id=rss_links.id
						GROUP BY date(items.created_at), rss_links.name
						) as t2 on t1.n1=t2.n group by n order by fc desc;

有点复杂，也有点冗余，也很慢，但是可以后面优化。

不用开方（实际是我嫌麻烦）就能看出来，这一个星期多的数据方差差距大的是一些不可言喻的站点，这说明了什么也是不可言喻的，当然了，人都是趋利避害的，西方不亮东方亮嘛