从行值获取百分比 - Get percentage from row values

我正在使用PHP与ORACLE。

SELECT AIP.TERMID, (select count(RAU.TERMNAME) FROM REPORT_API_USAGE RAU WHERE RAU.TERMID = AIP.TERMID AND RAU.VOCID = 21) as page_views , COUNT(case when aip.ctype = 'download' then 1 else null end) as page_download , COUNT(case when aip.ctype = 'event' then 1 else null end) as page_event FROM REPORT_API_PAGES AIP GROUP BY AIP.TERMID

这是我的查询以获得详细信息。我需要找到page_views, page_download， &page_event

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE REPORT_API_USAGE
    ("VOCID" int, "TERMNAME" varchar2(9))
;
INSERT ALL 
    INTO REPORT_API_USAGE ("VOCID", "TERMNAME")
         VALUES (21, 'blah')
    INTO REPORT_API_USAGE ("VOCID", "TERMNAME")
         VALUES (21, 'blah')
    INTO REPORT_API_USAGE ("VOCID", "TERMNAME")
         VALUES (21, 'blah')
    INTO REPORT_API_USAGE ("VOCID", "TERMNAME")
         VALUES (21, 'blah')
    INTO REPORT_API_USAGE ("VOCID", "TERMNAME")
         VALUES (21, 'blah')
    INTO REPORT_API_USAGE ("VOCID", "TERMNAME")
         VALUES (21, 'blah blah')
    INTO REPORT_API_USAGE ("VOCID", "TERMNAME")
         VALUES (21, 'blah blah')
    INTO REPORT_API_USAGE ("VOCID", "TERMNAME")
         VALUES (21, 'blah blah')
    INTO REPORT_API_USAGE ("VOCID", "TERMNAME")
         VALUES (21, 'blah blah')
    INTO REPORT_API_USAGE ("VOCID", "TERMNAME")
         VALUES (21, 'blah blah')
    INTO REPORT_API_USAGE ("VOCID", "TERMNAME")
         VALUES (21, 'blah blah')
    INTO REPORT_API_USAGE ("VOCID", "TERMNAME")
         VALUES (21, 'blah blah')
    INTO REPORT_API_USAGE ("VOCID", "TERMNAME")
         VALUES (21, 'blah blah')
SELECT * FROM dual
;

查询1

SELECT
      TERMNAME
    , COUNT(TERMNAME) as count_of
    , (COUNT(TERMNAME) * 100.0) / MAX(TOTCOUNT) as pct
FROM (
    SELECT
          AP.TERMNAME
        , COUNT(AP.TERMNAME) OVER() as TOTCOUNT
    FROM REPORT_API_USAGE AP
    WHERE AP.VOCID = '21'
  ) 
GROUP BY TERMNAME

结果

|  TERMNAME | COUNT_OF |               PCT |
|-----------|----------|-------------------|
|      blah |        5 | 38.46153846153846 |
| blah blah |        8 | 61.53846153846154 |

长字符串版本:

SELECT TERMNAME , COUNT(TERMNAME) as count_of , (COUNT(TERMNAME) * 100.0) / MAX(TOTCOUNT) as pct FROM ( SELECT AP.TERMNAME , COUNT(AP.TERMNAME) OVER() as TOTCOUNT FROM REPORT_API_USAGE AP WHERE AP.VOCID = '21' ) GROUP BY TERMNAME

生成要处理的示例数据是请求者应该提供的，因为我们必须在很大程度上猜测表中的内容。带着许多保留意见，我创建了一个示例并尝试了以下方法。

注意，我只提供SQL部分，您必须将其操作成合适的PHP。

SQL小提琴

Oracle 11g R2 Schema Setup:

CREATE TABLE REPORT_API_USAGE
    (TERMID int, VOCID int, TERMNAME varchar2(1))
;
INSERT ALL 
    INTO REPORT_API_USAGE ("TERMID", "VOCID", "TERMNAME")
         VALUES (1, 21, 'x')
    INTO REPORT_API_USAGE ("TERMID", "VOCID", "TERMNAME")
         VALUES (1, 21, 'x')
    INTO REPORT_API_USAGE ("TERMID", "VOCID", "TERMNAME")
         VALUES (1, 21, 'x')
    INTO REPORT_API_USAGE ("TERMID", "VOCID", "TERMNAME")
         VALUES (1, 21, 'x')
    INTO REPORT_API_USAGE ("TERMID", "VOCID", "TERMNAME")
         VALUES (1, 21, 'x')
    INTO REPORT_API_USAGE ("TERMID", "VOCID", "TERMNAME")
         VALUES (1, 21, 'x')
    INTO REPORT_API_USAGE ("TERMID", "VOCID", "TERMNAME")
         VALUES (1, 21, 'x')
    INTO REPORT_API_USAGE ("TERMID", "VOCID", "TERMNAME")
         VALUES (1, 21, 'x')
    INTO REPORT_API_USAGE ("TERMID", "VOCID", "TERMNAME")
         VALUES (1, 21, 'x')
SELECT * FROM dual
;
CREATE TABLE REPORT_API_PAGES
    (TERMID int, CTYPE varchar2(8))
;
INSERT ALL 
    INTO REPORT_API_PAGES ("TERMID", "CTYPE")
         VALUES (1, 'download')
    INTO REPORT_API_PAGES ("TERMID", "CTYPE")
         VALUES (1, 'event')
    INTO REPORT_API_PAGES ("TERMID", "CTYPE")
         VALUES (1, 'download')
    INTO REPORT_API_PAGES ("TERMID", "CTYPE")
         VALUES (1, 'event')
    INTO REPORT_API_PAGES ("TERMID", "CTYPE")
         VALUES (1, 'download')
    INTO REPORT_API_PAGES ("TERMID", "CTYPE")
         VALUES (1, 'event')
    INTO REPORT_API_PAGES ("TERMID", "CTYPE")
         VALUES (1, 'download')
    INTO REPORT_API_PAGES ("TERMID", "CTYPE")
         VALUES (1, 'event')
SELECT * FROM dual
;

查询1

SELECT
      AIP.TERMID
    , RAU.page_views
    , COUNT(CASE
            WHEN aip.ctype = 'download' THEN 1
            ELSE NULL
      END) AS page_download
    , COUNT(CASE
            WHEN aip.ctype = 'event' THEN 1
            ELSE NULL
      END) AS page_event
    , (COUNT(CASE
            WHEN aip.ctype = 'download' THEN 1
            ELSE NULL
      END) * 100.0) / RAU.page_views as dnload_pt
    , (COUNT(CASE
            WHEN aip.ctype = 'event' THEN 1
            ELSE NULL
      END) * 100.0) / RAU.page_views as event_pct
FROM REPORT_API_PAGES AIP
LEFT JOIN (
      SELECT
            TERMID
          , COUNT(*) AS page_views
      FROM REPORT_API_USAGE
      WHERE VOCID = 21
      GROUP BY TERMID
          ) RAU ON RAU.TERMID = AIP.TERMID
GROUP BY
      AIP.TERMID
    , RAU.page_views

结果

| TERMID | PAGE_VIEWS | PAGE_DOWNLOAD | PAGE_EVENT |         DNLOAD_PT |         EVENT_PCT |
|--------|------------|---------------|------------|-------------------|-------------------|
|      1 |          9 |             4 |          4 | 44.44444444444444 | 44.44444444444444 |