数据分析二

作者: knock | 来源:发表于2022-01-18 11:16 被阅读0次

2019-09-14 分析lianjia数据（四）——Power
2019-10-03 分析lianjia数据（五）——生成词云图
20190812SPSS学习心得(一)
数据分析学习（一）
医学生物信息学：基因芯片数据挖掘分析表达差异基因
数据分析第二天入门学习
英国零售商销售分析
Python电商数据清洗及分析
数据分析是干什么的？
在线教育描述性统计分析报告

SELECT 
ftl.province,
-- 身高平均值
(SELECT  CAST(AVG(ftl1.report_one) AS DECIMAL(10,2))  from fo_testing_log ftl1 where  ftl1.testing_foundation_id=1  and ftl1.grade_type='2' and  ftl1.province=ftl.province  and ftl1.createtime >= 1609430400 and ftl1.createtime <= 1640966399 ) as xiaoban_shengao,
(SELECT  CAST(AVG(ftl1.report_one ) AS DECIMAL(10,2))  from fo_testing_log ftl1 where  ftl1.testing_foundation_id=1  and ftl1.grade_type='1' and  ftl1.province=ftl.province  and ftl1.createtime >= 1609430400 and ftl1.createtime <= 1640966399 ) as zhongban_shengao,
(SELECT  CAST(AVG(ftl1.report_one) AS DECIMAL(10,2))  from fo_testing_log ftl1 where  ftl1.testing_foundation_id=1  and ftl1.grade_type='0' and  ftl1.province=ftl.province  and ftl1.createtime >= 1609430400 and ftl1.createtime <= 1640966399 ) as daban_shengao,
-- 体重平均值
(SELECT  CAST(AVG(ftl1.report_one) AS DECIMAL(10,2))  from fo_testing_log ftl1 where  ftl1.testing_foundation_id=2  and ftl1.grade_type='2' and  ftl1.province=ftl.province  and ftl1.createtime >= 1609430400 and ftl1.createtime <= 1640966399 ) as xiaoban_tizhong,
(SELECT  CAST(AVG(ftl1.report_one) AS DECIMAL(10,2))  from fo_testing_log ftl1 where  ftl1.testing_foundation_id=2 and ftl1.grade_type='1' and  ftl1.province=ftl.province  and ftl1.createtime >= 1609430400 and ftl1.createtime <= 1640966399 ) as zhongban_tizhong,
(SELECT  CAST(AVG(ftl1.report_one) AS DECIMAL(10,2))  from fo_testing_log ftl1 where  ftl1.testing_foundation_id=2  and ftl1.grade_type='0' and  ftl1.province=ftl.province  and ftl1.createtime >= 1609430400 and ftl1.createtime <= 1640966399 ) as daban_tizhong,
-- 臂展平均值
(SELECT  CAST(AVG(ftl1.report_one) AS DECIMAL(10,2))  from fo_testing_log ftl1 where  ftl1.testing_foundation_id=4  and ftl1.grade_type='2' and  ftl1.province=ftl.province  and ftl1.createtime >= 1609430400 and ftl1.createtime <= 1640966399 ) as xiaoban_bizhan,
(SELECT  CAST(AVG(ftl1.report_one) AS DECIMAL(10,2))  from fo_testing_log ftl1 where  ftl1.testing_foundation_id=4 and ftl1.grade_type='1' and  ftl1.province=ftl.province  and ftl1.createtime >= 1609430400 and ftl1.createtime <= 1640966399 ) as zhongban_bizhan,
(SELECT  CAST(AVG(ftl1.report_one) AS DECIMAL(10,2))  from fo_testing_log ftl1 where  ftl1.testing_foundation_id=4  and ftl1.grade_type='0' and  ftl1.province=ftl.province  and ftl1.createtime >= 1609430400 and ftl1.createtime <= 1640966399 ) as daban_bizhan,
-- 胸围平均值
(SELECT  CAST(AVG(ftl1.report_one) AS DECIMAL(10,2))  from fo_testing_log ftl1 where  ftl1.testing_foundation_id=6  and ftl1.grade_type='2' and  ftl1.province=ftl.province  and ftl1.createtime >= 1609430400 and ftl1.createtime <= 1640966399 ) as xiaoban_xiongwei,
(SELECT  CAST(AVG(ftl1.report_one) AS DECIMAL(10,2))  from fo_testing_log ftl1 where  ftl1.testing_foundation_id=6 and ftl1.grade_type='1' and  ftl1.province=ftl.province  and ftl1.createtime >= 1609430400 and ftl1.createtime <= 1640966399 ) as zhongban_xiongwei,
(SELECT  CAST(AVG(ftl1.report_one) AS DECIMAL(10,2))  from fo_testing_log ftl1 where  ftl1.testing_foundation_id=6  and ftl1.grade_type='0' and  ftl1.province=ftl.province  and ftl1.createtime >= 1609430400 and ftl1.createtime <= 1640966399 ) as daban_xiongwei,
-- 肺活量平均值
(SELECT  CAST(AVG(ftl1.report_one) AS DECIMAL(10,2))  from fo_testing_log ftl1 where  ftl1.testing_foundation_id=5  and ftl1.grade_type='2' and  ftl1.province=ftl.province  and ftl1.createtime >= 1609430400 and ftl1.createtime <= 1640966399 ) as xiaoban_feihuoliang,
(SELECT  CAST(AVG(ftl1.report_one) AS DECIMAL(10,2))  from fo_testing_log ftl1 where  ftl1.testing_foundation_id=5 and ftl1.grade_type='1' and  ftl1.province=ftl.province  and ftl1.createtime >= 1609430400 and ftl1.createtime <= 1640966399 ) as zhongban_feihuoliang,
(SELECT  CAST(AVG(ftl1.report_one) AS DECIMAL(10,2))  from fo_testing_log ftl1 where  ftl1.testing_foundation_id=5  and ftl1.grade_type='0' and  ftl1.province=ftl.province  and ftl1.createtime >= 1609430400 and ftl1.createtime <= 1640966399 ) as daban_feihuoliang,
-- 左眼视力平均值,右眼视力平均值
(SELECT  CAST(AVG(SUBSTRING_INDEX(ftl1.report_one,'/',1)) AS DECIMAL(10,2))  from fo_testing_log ftl1 where  ftl1.testing_foundation_id=3  and ftl1.grade_type='2' and  ftl1.province=ftl.province  and ftl1.createtime >= 1609430400 and ftl1.createtime <= 1640966399 ) as  xiaoban_zuoyan,
(SELECT  CAST(AVG(SUBSTRING_INDEX(ftl1.report_one,'/',-1)) AS DECIMAL(10,2))  from fo_testing_log ftl1 where  ftl1.testing_foundation_id=3  and ftl1.grade_type='2' and  ftl1.province=ftl.province  and ftl1.createtime >= 1609430400 and ftl1.createtime <= 1640966399 ) as  xiaoban_youyan,
(SELECT  CAST(AVG(SUBSTRING_INDEX(ftl1.report_one,'/',1)) AS DECIMAL(10,2))   from fo_testing_log ftl1 where  ftl1.testing_foundation_id=3 and ftl1.grade_type='1' and  ftl1.province=ftl.province  and ftl1.createtime >= 1609430400 and ftl1.createtime <= 1640966399 ) as zhongban_zuoyan,
(SELECT  CAST(AVG(SUBSTRING_INDEX(ftl1.report_one,'/',-1)) AS DECIMAL(10,2))   from fo_testing_log ftl1 where  ftl1.testing_foundation_id=3 and ftl1.grade_type='1' and  ftl1.province=ftl.province  and ftl1.createtime >= 1609430400 and ftl1.createtime <= 1640966399 ) as zhongban_youyan,
(SELECT  CAST(AVG(SUBSTRING_INDEX(ftl1.report_one,'/',1)) AS DECIMAL(10,2))   from fo_testing_log ftl1 where  ftl1.testing_foundation_id=3  and ftl1.grade_type='0' and  ftl1.province=ftl.province  and ftl1.createtime >= 1609430400 and ftl1.createtime <= 1640966399 ) as daban_zuoyan,
(SELECT  CAST(AVG(SUBSTRING_INDEX(ftl1.report_one,'/',-1)) AS DECIMAL(10,2))   from fo_testing_log ftl1 where  ftl1.testing_foundation_id=3  and ftl1.grade_type='0' and  ftl1.province=ftl.province  and ftl1.createtime >= 1609430400 and ftl1.createtime <= 1640966399 ) as daban_youyan,
-- 坐位体前屈平均值
(SELECT  CAST(AVG(ftl1.report_one) AS DECIMAL(10,2))  from fo_testing_log ftl1 where  ftl1.testing_foundation_id=8  and ftl1.grade_type='2' and  ftl1.province=ftl.province  and ftl1.createtime >= 1609430400 and ftl1.createtime <= 1640966399 ) as xiaoban_zuoweiti,
(SELECT  CAST(AVG(ftl1.report_one) AS DECIMAL(10,2))  from fo_testing_log ftl1 where  ftl1.testing_foundation_id=8 and ftl1.grade_type='1' and  ftl1.province=ftl.province  and ftl1.createtime >= 1609430400 and ftl1.createtime <= 1640966399 ) as zhongban_zuoweiti,
(SELECT  CAST(AVG(ftl1.report_one) AS DECIMAL(10,2))  from fo_testing_log ftl1 where  ftl1.testing_foundation_id=8  and ftl1.grade_type='0' and  ftl1.province=ftl.province  and ftl1.createtime >= 1609430400 and ftl1.createtime <= 1640966399 ) as daban_zuoweiti,
-- 体脂率平均值
(SELECT  CAST(AVG(ftl1.report_one) AS DECIMAL(10,2))  from fo_testing_log ftl1 where  ftl1.testing_foundation_id=7  and ftl1.grade_type='2' and  ftl1.province=ftl.province  and ftl1.createtime >= 1609430400 and ftl1.createtime <= 1640966399 ) as xiaoban_tizhilv,
(SELECT  CAST(AVG(ftl1.report_one) AS DECIMAL(10,2))  from fo_testing_log ftl1 where  ftl1.testing_foundation_id=7 and ftl1.grade_type='1' and  ftl1.province=ftl.province  and ftl1.createtime >= 1609430400 and ftl1.createtime <= 1640966399 ) as zhongban_tizhilv,
(SELECT  CAST(AVG(ftl1.report_one) AS DECIMAL(10,2))  from fo_testing_log ftl1 where  ftl1.testing_foundation_id=7  and ftl1.grade_type='0' and  ftl1.province=ftl.province  and ftl1.createtime >= 1609430400 and ftl1.createtime <= 1640966399 ) as daban_tizhilv,
-- 跑动平均值
(SELECT  CAST(AVG(ftl1.report_three) AS DECIMAL(10,2))  from fo_testing_log ftl1 where  ftl1.testing_sports_id=10  and ftl1.grade_type='2' and  ftl1.province=ftl.province  and ftl1.createtime >= 1609430400 and ftl1.createtime <= 1640966399 ) as xiaoban_paodong,
(SELECT  CAST(AVG(ftl1.report_three) AS DECIMAL(10,2))  from fo_testing_log ftl1 where  ftl1.testing_sports_id=9 and ftl1.grade_type='1' and  ftl1.province=ftl.province  and ftl1.createtime >= 1609430400 and ftl1.createtime <= 1640966399 ) as zhongban_paodong,
(SELECT  CAST(AVG(ftl1.report_three) AS DECIMAL(10,2))  from fo_testing_log ftl1 where  ftl1.testing_sports_id=1  and ftl1.grade_type='0' and  ftl1.province=ftl.province  and ftl1.createtime >= 1609430400 and ftl1.createtime <= 1640966399 ) as daban_paodong,
-- 跳跃平均值
(SELECT  CAST(AVG(ftl1.report_three) AS DECIMAL(10,2))  from fo_testing_log ftl1 where  ftl1.testing_sports_id=12  and ftl1.grade_type='2' and  ftl1.province=ftl.province  and ftl1.createtime >= 1609430400 and ftl1.createtime <= 1640966399 ) as xiaoban_tiaoyue,
(SELECT  CAST(AVG(ftl1.report_three) AS DECIMAL(10,2))  from fo_testing_log ftl1 where  ftl1.testing_sports_id=11 and ftl1.grade_type='1' and  ftl1.province=ftl.province  and ftl1.createtime >= 1609430400 and ftl1.createtime <= 1640966399 ) as zhongban_tiaoyue,
(SELECT  CAST(AVG(ftl1.report_three) AS DECIMAL(10,2))  from fo_testing_log ftl1 where  ftl1.testing_sports_id=2  and ftl1.grade_type='0' and  ftl1.province=ftl.province  and ftl1.createtime >= 1609430400 and ftl1.createtime <= 1640966399 ) as daban_tiaoyue,
-- 敏捷平均值
(SELECT  CAST(AVG(ftl1.report_three) AS DECIMAL(10,2))  from fo_testing_log ftl1 where  ftl1.testing_sports_id=14  and ftl1.grade_type='2' and  ftl1.province=ftl.province  and ftl1.createtime >= 1609430400 and ftl1.createtime <= 1640966399 ) as xiaoban_mingjie,
(SELECT  CAST(AVG(ftl1.report_three) AS DECIMAL(10,2))  from fo_testing_log ftl1 where  ftl1.testing_sports_id=13 and ftl1.grade_type='1' and  ftl1.province=ftl.province  and ftl1.createtime >= 1609430400 and ftl1.createtime <= 1640966399 ) as zhongban_mingjie,
(SELECT  CAST(AVG(ftl1.report_three) AS DECIMAL(10,2))  from fo_testing_log ftl1 where  ftl1.testing_sports_id=3  and ftl1.grade_type='0' and  ftl1.province=ftl.province  and ftl1.createtime >= 1609430400 and ftl1.createtime <= 1640966399 ) as daban_mingjie,
-- 协调平均值
(SELECT  CAST(AVG(ftl1.report_three) AS DECIMAL(10,2))  from fo_testing_log ftl1 where  ftl1.testing_sports_id=18  and ftl1.grade_type='2' and  ftl1.province=ftl.province  and ftl1.createtime >= 1609430400 and ftl1.createtime <= 1640966399 ) as xiaoban_xietiao,
(SELECT  CAST(AVG(ftl1.report_three) AS DECIMAL(10,2))  from fo_testing_log ftl1 where  ftl1.testing_sports_id=17 and ftl1.grade_type='1' and  ftl1.province=ftl.province  and ftl1.createtime >= 1609430400 and ftl1.createtime <= 1640966399 ) as zhongban_xietiao,
(SELECT  CAST(AVG(ftl1.report_three) AS DECIMAL(10,2))  from fo_testing_log ftl1 where  ftl1.testing_sports_id=5  and ftl1.grade_type='0' and  ftl1.province=ftl.province  and ftl1.createtime >= 1609430400 and ftl1.createtime <= 1640966399 ) as daban_xietiao,
-- 力量平均值
(SELECT  CAST(AVG(ftl1.report_three) AS DECIMAL(10,2))  from fo_testing_log ftl1 where  ftl1.testing_sports_id=20  and ftl1.grade_type='2' and  ftl1.province=ftl.province  and ftl1.createtime >= 1609430400 and ftl1.createtime <= 1640966399 ) as xiaoban_liliang,
(SELECT  CAST(AVG(ftl1.report_three) AS DECIMAL(10,2))  from fo_testing_log ftl1 where  ftl1.testing_sports_id=19 and ftl1.grade_type='1' and  ftl1.province=ftl.province  and ftl1.createtime >= 1609430400 and ftl1.createtime <= 1640966399 ) as zhongban_liliang,
(SELECT  CAST(AVG(ftl1.report_three) AS DECIMAL(10,2))  from fo_testing_log ftl1 where  ftl1.testing_sports_id=6  and ftl1.grade_type='0' and  ftl1.province=ftl.province  and ftl1.createtime >= 1609430400 and ftl1.createtime <= 1640966399 ) as daban_liliang,
--  左脚平衡平均值,右脚平衡平均值
(SELECT  CAST(AVG(SUBSTRING_INDEX(ftl1.report_three,'/',1)) AS DECIMAL(10,2))  from fo_testing_log ftl1 where  ftl1.testing_sports_id=16  and ftl1.grade_type='2' and  ftl1.province=ftl.province  and ftl1.createtime >= 1609430400 and ftl1.createtime <= 1640966399 ) as xiaoban_zuojiaopingheng,
(SELECT  CAST(AVG(SUBSTRING_INDEX(ftl1.report_three,'/',-1)) AS DECIMAL(10,2))  from fo_testing_log ftl1 where  ftl1.testing_sports_id=16  and ftl1.grade_type='2' and  ftl1.province=ftl.province  and ftl1.createtime >= 1609430400 and ftl1.createtime <= 1640966399 ) as xiaoban_youjiaopingheng,
(SELECT  CAST(AVG(SUBSTRING_INDEX(ftl1.report_three,'/',1)) AS DECIMAL(10,2))   from fo_testing_log ftl1 where  ftl1.testing_sports_id=15 and ftl1.grade_type='1' and  ftl1.province=ftl.province  and ftl1.createtime >= 1609430400 and ftl1.createtime <= 1640966399 ) as zhongban_zuojiaopingheng,
(SELECT  CAST(AVG(SUBSTRING_INDEX(ftl1.report_three,'/',-1)) AS DECIMAL(10,2))   from fo_testing_log ftl1 where  ftl1.testing_sports_id=15 and ftl1.grade_type='1' and  ftl1.province=ftl.province  and ftl1.createtime >= 1609430400 and ftl1.createtime <= 1640966399 ) as zhongban_youjiaopingheng,
(SELECT  CAST(AVG(SUBSTRING_INDEX(ftl1.report_three,'/',1)) AS DECIMAL(10,2))   from fo_testing_log ftl1 where  ftl1.testing_sports_id=4  and ftl1.grade_type='0' and  ftl1.province=ftl.province  and ftl1.createtime >= 1609430400 and ftl1.createtime <= 1640966399 ) as daban_zuojiaopingheng,
(SELECT  CAST(AVG(SUBSTRING_INDEX(ftl1.report_three,'/',-1)) AS DECIMAL(10,2))   from fo_testing_log ftl1 where  ftl1.testing_sports_id=4  and ftl1.grade_type='0' and  ftl1.province=ftl.province  and ftl1.createtime >= 1609430400 and ftl1.createtime <= 1640966399 ) as daban_youjiaopingheng,
--  物体控制（手）平均值
(SELECT  CAST(AVG(ftl1.report_three) AS DECIMAL(10,2))  from fo_testing_log ftl1 where  ftl1.testing_sports_id=22  and ftl1.grade_type='2' and  ftl1.province=ftl.province  and ftl1.createtime >= 1609430400 and ftl1.createtime <= 1640966399 ) as xiaoban_wutikongzhishou,
(SELECT  CAST(AVG(ftl1.report_three) AS DECIMAL(10,2))  from fo_testing_log ftl1 where  ftl1.testing_sports_id=21 and ftl1.grade_type='1' and  ftl1.province=ftl.province  and ftl1.createtime >= 1609430400 and ftl1.createtime <= 1640966399 ) as zhongban_wutikongzhishou,
(SELECT  CAST(AVG(ftl1.report_three) AS DECIMAL(10,2))  from fo_testing_log ftl1 where  ftl1.testing_sports_id=7  and ftl1.grade_type='0' and  ftl1.province=ftl.province  and ftl1.createtime >= 1609430400 and ftl1.createtime <= 1640966399 ) as daban_wutikongzhishou, 
--  物体控制（脚）平均值
(SELECT  CAST(AVG(ftl1.report_three) AS DECIMAL(10,2))  from fo_testing_log ftl1 where  ftl1.testing_sports_id=27  and ftl1.grade_type='2' and  ftl1.province=ftl.province  and ftl1.createtime >= 1609430400 and ftl1.createtime <= 1640966399 ) as xiaoban_wjiaoongzhijiao,
(SELECT  CAST(AVG(ftl1.report_three) AS DECIMAL(10,2))  from fo_testing_log ftl1 where  ftl1.testing_sports_id=26 and ftl1.grade_type='1' and  ftl1.province=ftl.province  and ftl1.createtime >= 1609430400 and ftl1.createtime <= 1640966399 ) as zhongban_wjiaoongzhijiao,
(SELECT  CAST(AVG(ftl1.report_three) AS DECIMAL(10,2))  from fo_testing_log ftl1 where  ftl1.testing_sports_id=25  and ftl1.grade_type='0' and  ftl1.province=ftl.province  and ftl1.createtime >= 1609430400 and ftl1.createtime <= 1640966399 ) as daban_wjiaoongzhijiao
from fo_atte_school ftl
group by   ftl.province

2019-09-14 分析lianjia数据（四）——Power
分析lianjia房源数据（一）——Python数据清洗分析lianjia房源数据（二）——SPSS数据清洗分...
2019-10-03 分析lianjia数据（五）——生成词云图
分析lianjia房源数据（一）——Python数据清洗分析lianjia房源数据（二）——SPSS数据清洗分...
20190812SPSS学习心得(一)
一、数据分析归类结构化数据分析和测量数据分析二、数据分析应用领域归类小样本：小数据分析，用加号连接，市场...
数据分析学习（一）
一、基础概要二、数据分析基础概念 2.1什么是数据分析？数据分析是用适当的方法对收集来的大量数据进行分析，...
医学生物信息学：基因芯片数据挖掘分析表达差异基因
基因芯片数据挖掘分析表达差异基因基因芯片数据分析（一）：芯片数据初探基因芯片数据分析（二）：读取芯片数据基因...
数据分析第二天入门学习
第二章结构为王--确定分析思路数据分析方法论主要从宏观角度指导如何数据分析，数据分析的前期规划，指导着后期数据...
英国零售商销售分析
目录一. 项目背景二. 数据探索三. 数据清洗四. 数据分析1. 消费趋势分析2. 用户行为分析3. 用户分类 (...
Python电商数据清洗及分析
一、数据来源本文使用python来分析一份电商数据，源数据可在下方评论获取。二、分析思路三、分析过程 3.1...
数据分析是干什么的？
数据分析是干什么的主要从两个纬度来理解？一是数据分析的作用;二是数据分析的工作内容。数据分析的作用单纯的谈数据...
在线教育描述性统计分析报告
确定分析思路在进行数据分析之前，先确定本次分析思路，具体如下一、提出问题二、理解数据数据来源：https:/...