有需求如下:
设计数据库表,存储网站受众年龄、省份、学历、收入分布。举例:
年龄数据可以查询出每个年龄的人数占总人数的百分比如下:
8岁以下x%
9岁x%
10岁x%
:
:
:
:
80岁及以上 x%考虑到可扩展性和查询方便,怎么设计数据库表和关系?
设计数据库表,存储网站受众年龄、省份、学历、收入分布。举例:
年龄数据可以查询出每个年龄的人数占总人数的百分比如下:
8岁以下x%
9岁x%
10岁x%
:
:
:
:
80岁及以上 x%考虑到可扩展性和查询方便,怎么设计数据库表和关系?
insert into tb values(0,'北京','',0)
insert into tb values(1,'北京','',0)
insert into tb values(8,'北京','',0)
insert into tb values(9,'北京','',0)
insert into tb values(10,'北京','',0)
insert into tb values(11,'北京','',0)
insert into tb values(12,'北京','',0)
insert into tb values(12,'北京','',0)
insert into tb values(12,'北京','',0)
insert into tb values(12,'北京','',0)
insert into tb values(12,'北京','',0)
insert into tb values(12,'北京','',0)
insert into tb values(80,'北京','',0)
insert into tb values(81,'北京','',0)
goselect age,count(*)*1.0/(select count(*) total from tb) cnt from tb group by age drop table tb/*
age cnt
----------- --------------------------
0 .071428571428
1 .071428571428
8 .071428571428
9 .071428571428
10 .071428571428
11 .071428571428
12 .428571428571
80 .071428571428
81 .071428571428(所影响的行数为 9 行)
*/
insert into tb values(0,'北京','',0)
insert into tb values(1,'北京','',0)
insert into tb values(8,'北京','',0)
insert into tb values(9,'北京','',0)
insert into tb values(10,'北京','',0)
insert into tb values(11,'北京','',0)
insert into tb values(12,'北京','',0)
insert into tb values(12,'北京','',0)
insert into tb values(12,'北京','',0)
insert into tb values(12,'北京','',0)
insert into tb values(12,'北京','',0)
insert into tb values(12,'北京','',0)
insert into tb values(80,'北京','',0)
insert into tb values(81,'北京','',0)
goselect age='8岁以下',count(*)*1.0/(select count(*) total from tb) cnt from tb where age <= 8
union all
select age=cast(age as varchar) + '岁',count(*)*1.0/(select count(*) total from tb) cnt from tb where age > 8 and age < 80 group by age
union all
select age='80岁以上',count(*)*1.0/(select count(*) total from tb) cnt from tb where age >= 80 drop table tb/*
age cnt
-------------------------------- --------------------------
8岁以下 .214285714285
9岁 .071428571428
10岁 .071428571428
11岁 .071428571428
12岁 .428571428571
80岁以上 .142857142857(所影响的行数为 6 行)
*/
但是我的受众信息实际上是已经知道分布情况了,要往网站表里来添加。