o******n 发帖数: 511 | 1 这个表长这样:
client site month visit_count uu
2 1 2011-01-01 1 300
2 1 2011-01-01 1 100
2 1 2011-01-01 1 100
2 2 2011-01-01 2 150
2 2 2011-01-01 2 50
2 3 2011-01-01 2 50
2 2 2011-01-01 3 400
2 3 2011-01-01 3 120
2 3 2011-01-01 3 120
2 3 2011-01-01 4 420
uu是unique users
每个client有好几个site,每个site每个月里,visit_count从1到很多,每个visit_
count对应一个uu数
有两句sql query计算每个visit_count对应的uu数和percentage:
select sum(uu) as total_uu from tbl where client = ** and visit_count > 0
and month = ** and site = **
select visit_count - 1 as visit_count, uu, (1.0 * uu/ (1.0 * 2407)) * 100 as
per from tbl where client = ** and visit_count > 1 and month = ** and site
= **
忘说了,这个2407是第一句得到的total_uu
我想把这两句变成subquery得到每个client各个site每个月每个visit_count对应的uu
。我脑袋已经写昏了,请问要怎么写?谢谢。 |
s****e 发帖数: 54 | 2 if me,
I will define a variable to hold the vaule from first query, then set it to
the 2nd query.
create a sp
then exe the sp |
e****7 发帖数: 4387 | 3 或许我没理解对,大概可以写成这样:
Select Client
, Site
, Month
, Total_UU = SUM(UU)
, Per = (100.0 * SUM(Visit_Count*UU) / SUM(Visit_Count))
FROM ***
WHERE Client = **
AND Site = **
AND Month = **
GROUP BY Client, Site, Month
如果这个是web analytics 实际数据的话,我不是太理解。我做过好一阵子的web
analytics |
s****e 发帖数: 54 | 4 define @Sum integer
set @Sum=(select sum(uu) from tbl where client = ** and visit_count > 0
and month = ** and site = ** )
select visit_count - 1 as visit_count, uu, (1.0 * uu/ (1.0 * @SUM)) * 100 as
per from tbl where client = ** and visit_count > 1 and month = ** and site
= ** |
o******n 发帖数: 511 | 5 不好意思,我帖子没写清楚,这个是实际的数据,我就把那些数值变成整数而已。
我刚发现我昨天写的是对的,但我把意思理解错了,要算的不是访问一次的用户的百分
比而是两次的,怪不得我说数字怎么老对不上。
这是我自己现在改写好的。
这里和别人join
(select t1.client, t1.month, avg(t1.p) as percent_users_returning_once
from (select tbl.client, tbl.site, tbl.month,
case
when sum(tbl.uu)=0
then null
when sum(tbl.uu) is null
then null
else cast(sum(case when tbl.visit_count=2 then tbl.uu when tbl.
visit_count=1 then 0 when tbl.visit_count>2 then 0 end) as float)/sum(tbl.uu
) end as p
from tbl
group by tbl.client, tbl.site, tbl.month) t1
group by t1.client, t1.month)t2
on
**=t2.** and ** = t2.** |
o******n 发帖数: 511 | 6 谢谢小米。请问在哪种数据库里可以这样写?
as
【在 s****e 的大作中提到】 : define @Sum integer : set @Sum=(select sum(uu) from tbl where client = ** and visit_count > 0 : and month = ** and site = ** ) : select visit_count - 1 as visit_count, uu, (1.0 * uu/ (1.0 * @SUM)) * 100 as : per from tbl where client = ** and visit_count > 1 and month = ** and site : = **
|
o******n 发帖数: 511 | 7 谢谢。请问哪里觉得不对,是take average across all sites for 1 client那步吗?
【在 e****7 的大作中提到】 : 或许我没理解对,大概可以写成这样: : Select Client : , Site : , Month : , Total_UU = SUM(UU) : , Per = (100.0 * SUM(Visit_Count*UU) / SUM(Visit_Count)) : FROM *** : WHERE Client = ** : AND Site = ** : AND Month = **
|
e****7 发帖数: 4387 | 8
visit_count 是每个visitor 的page count吧,也就是第一行数据是说有300个用户看
了一页就走掉了。
percentage到底要的是什么呢?
可以理解为,每个visit_count 的流量占总流量的比例么?或者是每个visit_count 的
user count占总user count的比例。
【在 o******n 的大作中提到】 : 谢谢。请问哪里觉得不对,是take average across all sites for 1 client那步吗?
|
s****e 发帖数: 54 | 9 I use sql server 2008 R2
Sorry I made a mistake
to define variable in SQL is :
declare @Sum integer
【在 o******n 的大作中提到】 : 谢谢小米。请问在哪种数据库里可以这样写? : : as
|
o******n 发帖数: 511 | 10 嗯,就是最后一个的意思,其实这个是看user loyalty的,就是回来再看一次那个网站
的用户的百分比多少,返回两次的多少,etc。
【在 e****7 的大作中提到】 : : visit_count 是每个visitor 的page count吧,也就是第一行数据是说有300个用户看 : 了一页就走掉了。 : percentage到底要的是什么呢? : 可以理解为,每个visit_count 的流量占总流量的比例么?或者是每个visit_count 的 : user count占总user count的比例。
|
o******n 发帖数: 511 | 11 谢谢,我没用过sql server,现在用redshift,还从来没用过declare这么高级的功能:
-)
【在 s****e 的大作中提到】 : I use sql server 2008 R2 : Sorry I made a mistake : to define variable in SQL is : : declare @Sum integer
|