select TIMESTAMPADD(SQL_TSI_DAY, -30, @{V_date}{date'2013-12-05'}) FROM "A1 Bc Analysis" where "D00 Day More"."Date2" = @{V_yesterday}{date'2013-12-05'}
select TIMESTAMPADD(SQL_TSI_DAY, -30, @{V_date}{date'2013-12-05'}) FROM "A1 Bc Analysis"
SELECT TIMESTAMPADD(SQL_TSI_DAY, -30, "D00 Day"."Date") FROM "A1 Bc Analysis" where "D00 Day More"."Date2" = @{V_date}{date'2013-12-10'}
(case when '@{V_peroid}{onemonth}'='onemonth' then TIMESTAMPADD(SQL_TSI_DAY, -30, @{V_date}{date'2013-12-01'})
when '@{V_peroid}{onemonth}'='twomonth' then TIMESTAMPADD(SQL_TSI_DAY, -60, @{V_date}{date'2013-12-01'}) else TIMESTAMPADD(SQL_TSI_DAY, -10, @{V_date}
{date'2013-12-01'}) end)
"D00 Day"."Date" < @{V_date}{date'2013-12-01'} and "D00 Day"."Date" > TIMESTAMPADD(SQL_TSI_DAY, -30, @{V_date}{date'2013-12-01'})
SELECT TIMESTAMPADD(SQL_TSI_DAY, -30, "D00 Day"."Date") FROM "A1 Bc Analysis" where "D00 Day More"."Date2" = @{V_date}{date'2013-12-10'}
SELECT TIMESTAMPADD(SQL_TSI_DAY, 0, "D00 Day"."Date") FROM "A1 Bc Analysis" where "D00 Day More"."Date2" = @{V_yesterday}{date'2013-12-05'}
Prom Week Peroid
dukang@maxthon.net
maxiaofei@maxthon.net
zhoujianguang@maxthon.net
cloud_data@maxthon.net
One Month
Two Month
Three Month
Six Month
Year
"D00 Day"."Date" < @{V_date}{date'2013-12-01'} and "D00 Day"."Date" >(
case when '@{V_peroid}{One Month}'='One Month' then TIMESTAMPADD(SQL_TSI_DAY, -30, @{V_date}{date'2013-12-01'})
when '@{V_peroid}{One Month}'='Two Month' then TIMESTAMPADD(SQL_TSI_DAY, -60, @{V_date}{date'2013-12-01'})
when '@{V_peroid}{One Month}'='Three Month' then TIMESTAMPADD(SQL_TSI_DAY, -60, @{V_date}{date'2013-12-01'})
when '@{V_peroid}{One Month}'='Six Month' then TIMESTAMPADD(SQL_TSI_DAY, -60, @{V_date}{date'2013-12-01'})
else TIMESTAMPADD(SQL_TSI_DAY, -5, @{V_date}{date'2013-12-01'}) end)
select "D03 Pn"."Pn" from "A1 Bc Analysis" where "D03 Pn"."Platform" = 'win' and "D03 Pn"."Insert From" in (select max("D03 Pn"."Insert From") from "A1 Bc
Analysis" where "D03 Pn"."Platform" = 'win')
Peroid
Week
Month
Three Month
Six Month
Year
1. 有哪些是目前质检体系发现不了的问题(发生过,只是 是没有事先察觉)
2. 哪些是管理层需要了解的但现有质检体系满足不了的
3. 现有的质检体系介绍,哪些规
select * from (
select t.platform as platform,
t.version_id as version_id,
t.version as oversion,
regexp_substr(t.version, '^[[:digit:]\.]') as bigver,
decode(regexp_substr(version, '^[[:digit:]\.]'),4,regexp_substr(version, '^[[:digit:]](\.[[:digit:]]+)+00$'),version) as version
from dim_version t
where regexp_substr(t.version, '^[[:digit:]](\.[[:digit:]]+)+$') is not null
and t.version != 'all'
)
where version is not null
select platform,pn,regexp_substr(t.pn, '^[[:alnum:]](\_)*([[:alnum:]]+)+$') ,pn_id from dim_pn t
select platform,
pn,
pn_id
from dim_pn t
where regexp_substr(t.pn, '^[:alnum:]*_*[[:alnum:]]*$') is not null and t.pn !='all'
select
distinct platform,regexp_extract(version,'^([1-4])\.',1), version
from
rc_pt
where
1=1
and accessDay >= '20130704'
and accessDay <= '20130704'
and datatype='first'
and product='online'
and platform in('anphone','win','anpad','')
and length(deviceid)=40
and regexp_extract(version,'^[1-4](\.)',1) != '.'
and version not rlike '(^[1-3]\.[0-9]+(\.[0-9]+){0,2}$)|(^4\.[0-9]{1,2}\.[0-9]{1,3}\.[1-9]{1,2}0?00$)';
select
distinct platform,regexp_extract(version,'^([1-4])\.',1), version
from
rc_pt
where
1=1
and accessDay >= '20130704'
and accessDay <= '20130704'
and datatype='first'
and product='online'
and platform in('win','mac','iphone','ipad','anpad','anphone')
and regexp_extract(version,'^[1-4](\.)',1) = '.'
and version rlike '(^[1-3]\.[0-9]+(\.[0-9]+){0,2}$)|(^4\.[0-9]{1,2}\.[0-9]{1,3}(\.[1-9]{1,2}0?00)?$)';
and length(deviceid)=40;
select
distinct platform,regexp_extract(version,'^([1-4])\.',1), version
from
rc_pt
where
1=1
and accessDay >= '20130704'
and accessDay <= '20130704'
and datatype='first'
and product='online'
and platform in('win','mac','iphone','ipad','anpad','anphone')
and version rlike '(^4\.[0-9]{1,2}\.[0-9]{1,2})|(^4\.[0-9]{1,2}\.[0-9]{1,2}\.[1-9]0?00$)'
and length(deviceid)=40;
select
distinct platform,regexp_extract(version,'^([1-4])\.',1), version
from
rc_pt
where
1=1
and accessDay >= '20130704'
and accessDay <= '20130704'
and datatype='first'
and product='online'
and platform in('win','mac','iphone','ipad','anpad','anphone')
and version REGEXP '^4(\.[0-9]{1,2}){1,2}(\.[1-9]{1,2}0?00$)?'
and length(deviceid)=40;
(^[1-3]\.[0-9]+(\.[0-9]+){0,2}$)|(^4\.[0-9]{1,2}\.[0-9]{1,3}(\.[1-9]{1,2}0?00$)?)
select
distinct platform,regexp_extract(version,'^([1-4])\.',1), version
from
rc_pt
where
1=1
and accessDay >= '20130704'
and accessDay <= '20130704'
and datatype='first'
and product='online'
and platform in('anphone')
and length(deviceid)=40
and version rlike '^[1-3](\.[0-9]+)+$';
(?(exp)yes|no)
^[1-9a-z]*_*[1-9a-z]*$
select
distinct platform,(case when platform='anphone' and (substr(pn,1,1)='1' or substr(pn,1,1)='2') then 'user-defined' else pn end) as pn
from
rc_pt
where
1=1
and accessDay >= '20130704'
and accessDay <= '20130704'
and datatype='oc'
and product='online'
and platform in('win','mac','iphone','ipad','anpad','anphone')
and length(deviceid)=40
and pn rlike '^[a-zA-Z0-9_]+$';
select TIMESTAMPADD(SQL_TSI_DAY, -30, @{V_date}{date'2013-12-05'}) FROM "A1 Bc Analysis"
SELECT TIMESTAMPADD(SQL_TSI_DAY, -30, "D00 Day"."Date") FROM "A1 Bc Analysis" where "D00 Day More"."Date2" = @{V_date}{date'2013-12-10'}
(case when '@{V_peroid}{onemonth}'='onemonth' then TIMESTAMPADD(SQL_TSI_DAY, -30, @{V_date}{date'2013-12-01'})
when '@{V_peroid}{onemonth}'='twomonth' then TIMESTAMPADD(SQL_TSI_DAY, -60, @{V_date}{date'2013-12-01'}) else TIMESTAMPADD(SQL_TSI_DAY, -10, @{V_date}
{date'2013-12-01'}) end)
"D00 Day"."Date" < @{V_date}{date'2013-12-01'} and "D00 Day"."Date" > TIMESTAMPADD(SQL_TSI_DAY, -30, @{V_date}{date'2013-12-01'})
SELECT TIMESTAMPADD(SQL_TSI_DAY, -30, "D00 Day"."Date") FROM "A1 Bc Analysis" where "D00 Day More"."Date2" = @{V_date}{date'2013-12-10'}
SELECT TIMESTAMPADD(SQL_TSI_DAY, 0, "D00 Day"."Date") FROM "A1 Bc Analysis" where "D00 Day More"."Date2" = @{V_yesterday}{date'2013-12-05'}
Prom Week Peroid
dukang@maxthon.net
maxiaofei@maxthon.net
zhoujianguang@maxthon.net
cloud_data@maxthon.net
One Month
Two Month
Three Month
Six Month
Year
"D00 Day"."Date" < @{V_date}{date'2013-12-01'} and "D00 Day"."Date" >(
case when '@{V_peroid}{One Month}'='One Month' then TIMESTAMPADD(SQL_TSI_DAY, -30, @{V_date}{date'2013-12-01'})
when '@{V_peroid}{One Month}'='Two Month' then TIMESTAMPADD(SQL_TSI_DAY, -60, @{V_date}{date'2013-12-01'})
when '@{V_peroid}{One Month}'='Three Month' then TIMESTAMPADD(SQL_TSI_DAY, -60, @{V_date}{date'2013-12-01'})
when '@{V_peroid}{One Month}'='Six Month' then TIMESTAMPADD(SQL_TSI_DAY, -60, @{V_date}{date'2013-12-01'})
else TIMESTAMPADD(SQL_TSI_DAY, -5, @{V_date}{date'2013-12-01'}) end)
select "D03 Pn"."Pn" from "A1 Bc Analysis" where "D03 Pn"."Platform" = 'win' and "D03 Pn"."Insert From" in (select max("D03 Pn"."Insert From") from "A1 Bc
Analysis" where "D03 Pn"."Platform" = 'win')
Peroid
Week
Month
Three Month
Six Month
Year
1. 有哪些是目前质检体系发现不了的问题(发生过,只是 是没有事先察觉)
2. 哪些是管理层需要了解的但现有质检体系满足不了的
3. 现有的质检体系介绍,哪些规
select * from (
select t.platform as platform,
t.version_id as version_id,
t.version as oversion,
regexp_substr(t.version, '^[[:digit:]\.]') as bigver,
decode(regexp_substr(version, '^[[:digit:]\.]'),4,regexp_substr(version, '^[[:digit:]](\.[[:digit:]]+)+00$'),version) as version
from dim_version t
where regexp_substr(t.version, '^[[:digit:]](\.[[:digit:]]+)+$') is not null
and t.version != 'all'
)
where version is not null
select platform,pn,regexp_substr(t.pn, '^[[:alnum:]](\_)*([[:alnum:]]+)+$') ,pn_id from dim_pn t
select platform,
pn,
pn_id
from dim_pn t
where regexp_substr(t.pn, '^[:alnum:]*_*[[:alnum:]]*$') is not null and t.pn !='all'
select
distinct platform,regexp_extract(version,'^([1-4])\.',1), version
from
rc_pt
where
1=1
and accessDay >= '20130704'
and accessDay <= '20130704'
and datatype='first'
and product='online'
and platform in('anphone','win','anpad','')
and length(deviceid)=40
and regexp_extract(version,'^[1-4](\.)',1) != '.'
and version not rlike '(^[1-3]\.[0-9]+(\.[0-9]+){0,2}$)|(^4\.[0-9]{1,2}\.[0-9]{1,3}\.[1-9]{1,2}0?00$)';
select
distinct platform,regexp_extract(version,'^([1-4])\.',1), version
from
rc_pt
where
1=1
and accessDay >= '20130704'
and accessDay <= '20130704'
and datatype='first'
and product='online'
and platform in('win','mac','iphone','ipad','anpad','anphone')
and regexp_extract(version,'^[1-4](\.)',1) = '.'
and version rlike '(^[1-3]\.[0-9]+(\.[0-9]+){0,2}$)|(^4\.[0-9]{1,2}\.[0-9]{1,3}(\.[1-9]{1,2}0?00)?$)';
and length(deviceid)=40;
select
distinct platform,regexp_extract(version,'^([1-4])\.',1), version
from
rc_pt
where
1=1
and accessDay >= '20130704'
and accessDay <= '20130704'
and datatype='first'
and product='online'
and platform in('win','mac','iphone','ipad','anpad','anphone')
and version rlike '(^4\.[0-9]{1,2}\.[0-9]{1,2})|(^4\.[0-9]{1,2}\.[0-9]{1,2}\.[1-9]0?00$)'
and length(deviceid)=40;
select
distinct platform,regexp_extract(version,'^([1-4])\.',1), version
from
rc_pt
where
1=1
and accessDay >= '20130704'
and accessDay <= '20130704'
and datatype='first'
and product='online'
and platform in('win','mac','iphone','ipad','anpad','anphone')
and version REGEXP '^4(\.[0-9]{1,2}){1,2}(\.[1-9]{1,2}0?00$)?'
and length(deviceid)=40;
(^[1-3]\.[0-9]+(\.[0-9]+){0,2}$)|(^4\.[0-9]{1,2}\.[0-9]{1,3}(\.[1-9]{1,2}0?00$)?)
select
distinct platform,regexp_extract(version,'^([1-4])\.',1), version
from
rc_pt
where
1=1
and accessDay >= '20130704'
and accessDay <= '20130704'
and datatype='first'
and product='online'
and platform in('anphone')
and length(deviceid)=40
and version rlike '^[1-3](\.[0-9]+)+$';
(?(exp)yes|no)
^[1-9a-z]*_*[1-9a-z]*$
select
distinct platform,(case when platform='anphone' and (substr(pn,1,1)='1' or substr(pn,1,1)='2') then 'user-defined' else pn end) as pn
from
rc_pt
where
1=1
and accessDay >= '20130704'
and accessDay <= '20130704'
and datatype='oc'
and product='online'
and platform in('win','mac','iphone','ipad','anpad','anphone')
and length(deviceid)=40
and pn rlike '^[a-zA-Z0-9_]+$';