如何用多余的列来实现查询，该列会累积非空列的数量 [英] how to implement query with extra column that accumulate number of non empty column

问题描述

假设下面有一个Oracle表，其中的列empty

Assume there is Oracle table with column empty below

------------------
| empty  | count |
|  true  |   0   |
|  true  |   0   |
|  false |   1   |
|  true  |   0   |
|  false |   2   |
|  true  |   0   |
|  false |   3   |
|  false |   4   |
|  true  |   0   |
|  false |   5   |
| .............  |
------------------

所需查询应返回数据empty列以及上面的count列，该列应记录empty列不为null的行的累积数量，并且结果应不受order by子句的影响

the desired query should return the data empty column along with a count column above which should record the accumulative amount of rows that empty column is not null, and result should not affected by order by clause

此外，如何改进查询以将结果显示为下表结果集?基本上让empty==true列保留上一个empty==false列的计数

furthermore, how to improve the query to show result as following table result set? basically let the empty==true column keep the count from previous empty==false column

------------------
| empty  | count |
|  true  |   0   |
|  true  |   0   |
|  false |   1   |
|  true  |   1   |
|  false |   2   |
|  true  |   2   |
|  false |   3   |
|  false |   4   |
|  true  |   4   |
|  true  |   4   |
|  true  |   4   |
|  false |   5   |
| .............  |
------------------

在此处更新第二个问题的查询

update the query here for the second question

WITH dates_list AS
  (SELECT TO_DATE('01-31-2018','MM-dd-yyyy') + ROWNUM - 1 AS DAY
  FROM dual
    CONNECT BY LEVEL <= (TO_DATE('03-31-2018','MM-dd-yyyy') - TO_DATE('01-31-2018','MM-dd-yyyy')+1)
  )
select all_date, week_date, count(case when flag is not null then 1 end) 
              over (partition by flag order by week_date) as cnt
from (
SELECT dates1.day as all_date, dates2.day as week_date, case when dates2.day is null then 0 else 1 end as flag
FROM dates_list dates1
LEFT JOIN
  (SELECT *
  FROM dates_list
  WHERE TO_CHAR(DAY,'D') NOT IN (7,1)
  ) dates2 ON dates1.day = dates2.day
)
order by all_date;

该线程中的样本表是上表的一个简单模拟，基本上，我尝试建立所有非周末日期的索引，但是任何周末日期都应保留先前的非周末索引(如果没有，则为0)

the sample table in this thread is an simple mock from above table, basically I try to have a index of all non-weekend dates but however any weekend dates should keep the previous non weekend index (0 if none)

推荐答案

您的第二个请求比第一个请求简单.您不会改善"第一个问题的解决方案，而是编写一个不同的(和更简单的)查询.

Your second request is simpler than the first. You don't "improve" the solution to the first problem, you write a different (and simpler) query.

假设排序由另一列ord(可以是数字，日期或其他):

Assuming the ordering is by another column ord (which may be numeric, or date, or whatever):

select empty, 
       count(case empty when 'false' then 1 end) over (order by ord) as cnt
from   .....

这篇关于如何用多余的列来实现查询，该列会累积非空列的数量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！