联合所有请求的百分比

作者: 你咖喱人biss嗷
发布时间: 2024-12-29 07:59:13 (1月前)
转自：

5 条回复

0#
回复此人
哈哈哈哈 | 2019-08-31 10-32

<div class =“post-text”itemprop =“text”> <P> 有几种方法可以做到这一点，可能或最好的方法部分取决于您的RDBMS。但是，这是一种方式。为简单起见，我将上面的查询替换为IntermediateResults表。实际上，您可以将查询与CTE，派生表，临时表或表变量一起使用。 </p> <pre> <code> CREATE TABLE IntermediateResults (OtherFiles INT, Site VARCHAR(20), Message VARCHAR(100)); GO INSERT INTO IntermediateResults (OtherFiles,Site,Message) VALUES (18,'BR-CTA','Files that are at least in Pool or MDA'); INSERT INTO IntermediateResults (OtherFiles,Site,Message) VALUES (324,'BR-CTA' ,'OTHERS'); INSERT INTO IntermediateResults (OtherFiles,Site,Message) VALUES (26,'BR-CTA-VPN','OTHERS'); INSERT INTO IntermediateResults (OtherFiles,Site,Message) VALUES (1,'IN-BLR','Files that are at least in Pool or MDA'); GO SELECT COALESCE(o.Site,p.Site) Site ,Ratio = CASE WHEN o.OtherFiles IS NULL THEN 0 WHEN p.OtherFiles IS NULL THEN 100 ELSE 100 * o.OtherFiles/(p.OtherFiles + o.OtherFiles) END FROM (SELECT * FROM IntermediateResults WHERE Message = 'OTHERS') o FULL JOIN (SELECT * FROM IntermediateResults WHERE Message <> 'OTHERS') p ON o.Site = p.Site </code> </pre> <P> 结果： </p> <pre> <code> BR-CTA 94 IN-BLR 0 BR-CTA-VPN 100 </code> </pre> <P> 编辑：如何用我的查询替换我的示例中的表的一个示例是使用a <a href="http://dba-oracle.com/t_oracle_subquery_factoring.htm" rel="nofollow"> 子查询因子 </A> 这就是Oracle调用TSQL公用表表达式或WITH构造。 </p> <pre> <code> WITH IntermediateResults AS ( /*your query here*/ ) SELECT COALESCE(o.Site,p.Site) Site ,Ratio = CASE WHEN o.OtherFiles IS NULL THEN 0 WHEN p.OtherFiles IS NULL THEN 100 ELSE 100 * o.OtherFiles/(p.OtherFiles + o.OtherFiles) END FROM (SELECT * FROM IntermediateResults WHERE Message = 'OTHERS') o FULL JOIN (SELECT * FROM IntermediateResults WHERE Message <> 'OTHERS') p ON o.Site = p.Site </code> </pre> </DIV>

编辑
1#
回复此人
Moso31 | 2019-08-31 10-32

<div class =“post-text”itemprop =“text”> <P> 我将数据放在临时表中以便于处理： </p> <pre> <code> DECLARE @tmp TABLE (readcount INT, site VARCHAR(40), origin VARCHAR(40)) INSERT INTO @TMP (readcount, site, origin) VALUES (18,'BR-CTA','Files that are at least in Pool or MDA') INSERT INTO @TMP (readcount, site, origin) VALUES (324,'BR-CTA','OTHERS') INSERT INTO @TMP (readcount, site, origin) VALUES (26,'BR-CTA-VPN','OTHERS') INSERT INTO @TMP (readcount, site, origin) VALUES (5,'CN-TSN-VPN','OTHERS') </code> </pre> <P> 我想你想要的是： </p> <pre> <code> SELECT percentage = ((SELECT readcount FROM @tmp T2 WHERE T2.site = T.site AND origin = 'OTHERS') * 100.0 / (SELECT SUM(readcount) FROM @tmp T3 WHERE t3.site = T.site GROUP BY SITE) ), site FROM @tmp t GROUP BY site </code> </pre> <P> 这导致了 </p> <pre> <code> 94.736842105263 BR-CTA 100.000000000000 BR-CTA-VPN 100.000000000000 CN-TSN-VPN </code> </pre> </DIV>

编辑
2#
回复此人
我头上有犄角 | 2019-08-31 10-32

<div class =“post-text”itemprop =“text”> <P> 从顶部开始，您不需要UNION ALL Query，您可以使用此查询检索数据。 </p> <P> 我省略了范围周期以便于阅读。 </p> <pre> <code> SELECT COUNT(TESTID) AS OTHER_FILES,SITE ,CASE WHEN FILE_ORIGIN_ID < 2 THEN 'Files that are at least in Pool or MDA' ELSE 'OTHERS' END AS validCondition FROM FILE_USAGE_LOG as pivot GROUP BY pivot.TESTID ,(CASE WHEN FILE_ORIGIN_ID < 2 THEN 'Files that are at least in Pool or MDA' ELSE 'OTHERS' END); </code> </pre> <P> 因此，你可以创建一个派生表，不用分组来获得所有的宇宙。 </p> <pre> <code> SELECT COUNT(TESTID) AS OTHER_FILES,SITE ,CASE WHEN FILE_ORIGIN_ID < 2 THEN 'Files that are at least in Pool or MDA' ELSE 'OTHERS' END AS validCondition, (COUNT(TESTID)/MAX(total))*100 AS ratio FROM FILE_USAGE_LOG as pivot CROSS JOIN(SELECT COUNT(TESTID) AS total FROM FILE_USAGE_LOG) AS u GROUP BY pivot.TESTID ,(CASE WHEN FILE_ORIGIN_ID < 2 THEN 'Files that are at least in Pool or MDA' ELSE 'OTHERS' END); </code> </pre> <P> 我希望这个答案可以帮到你问候。 </p> </DIV>

编辑
3#
回复此人
筱梨 | 2019-08-31 10-32

<div class =“post-text”itemprop =“text”> <P> 我认为这个查询在这里可能会有所帮助： </p> <pre> <code> with t as ( select site, count(case when dsc = 'MDA' then testid end) mda, count(case when dsc = 'OTH' then testid end) oth from ( select testid, site, case when exists ( select testid from file_usage_log where file_origin_id<2 and testid = ful.testid) then 'MDA' else 'OTH' end dsc from file_usage_log ful where latest_read between date '2015-05-01' and date '2015-06-01') group by site) select site, round(100*oth/(oth+mda)) percent from t </code> </pre> <P> <SUP> <a href="http://sqlfiddle.com/#!4/7cd187/1" rel="nofollow"> SQLFiddle </A> </SUP> </p> <P> 如果没有您的数据访问，很难验证计算的核心性，但对于我的示例，它是有效的。 </p> </DIV>

编辑

登录后才能参与评论