如何查找一个月中已使用的天数总数? [英] How do I find the total number of used days in a month?
问题描述
我得出的是一个月内使用某项服务的总天数. (Start_Date和End_Date-都包括在内)
样本数据1:
User Start_Date End_Date
A 01-Jun-2017 30-Jun-2017
B 06-Jun-2017 30-Jun-2017
回答:服务使用天数= 30天.
样本数据2:
User Start_Date End_Date
C 06-Jun-2017 10-Jun-2017
D 02-Jun-2017 02-Jun-2017
回答:服务使用天数= 6天.
如何编写代码以找到相同的代码，在SQL中比PLSQL更可取.
测试数据:
CREATE TABLE your_table ( usr, start_date, end_date ) AS (
SELECT 'A', DATE '2017-06-01', DATE '2017-06-03' FROM DUAL UNION ALL
SELECT 'B', DATE '2017-06-02', DATE '2017-06-04' FROM DUAL UNION ALL -- Overlaps previous
SELECT 'C', DATE '2017-06-06', DATE '2017-06-06' FROM DUAL UNION ALL
SELECT 'D', DATE '2017-06-07', DATE '2017-06-07' FROM DUAL UNION ALL -- Adjacent to previous
SELECT 'E', DATE '2017-06-11', DATE '2017-06-20' FROM DUAL UNION ALL
SELECT 'F', DATE '2017-06-14', DATE '2017-06-15' FROM DUAL UNION ALL -- Within previous
SELECT 'G', DATE '2017-06-22', DATE '2017-06-25' FROM DUAL UNION ALL
SELECT 'H', DATE '2017-06-24', DATE '2017-06-28' FROM DUAL UNION ALL -- Overlaps previous and next
SELECT 'I', DATE '2017-06-27', DATE '2017-06-30' FROM DUAL UNION ALL
SELECT 'J', DATE '2017-06-27', DATE '2017-06-28' FROM DUAL; -- Within H and I
查询:
SELECT SUM( days ) AS total_days
FROM (
SELECT dt - LAG( dt ) OVER ( ORDER BY dt ) + 1 AS days,
start_end
FROM (
SELECT dt,
CASE SUM( value ) OVER ( ORDER BY dt ASC, value DESC, ROWNUM ) * value
WHEN 1 THEN 'start'
WHEN 0 THEN 'end'
END AS start_end
FROM your_table
UNPIVOT ( dt FOR value IN ( start_date AS 1, end_date AS -1 ) )
)
WHERE start_end IS NOT NULL
)
WHERE start_end = 'end';
输出:
TOTAL_DAYS
----------
25
说明:
SELECT dt, value
FROM your_table
UNPIVOT ( dt FOR value IN ( start_date AS 1, end_date AS -1 ) )
这将UNPIVOT该表，以便开始日期和结束日期在同一列(dt)中，并为开始日期和结束日期分别指定+1和-1.
SELECT dt,
SUM( value ) OVER ( ORDER BY dt ASC, value DESC, ROWNUM ) AS total,
value
FROM your_table
UNPIVOT ( dt FOR value IN ( start_date AS 1, end_date AS -1 ) )
将给出开始和结束日期以及这些生成值的累积总和.范围的开始将始终具有value=1和total=1，范围的结束将始终具有total=0.如果日期在范围的中间，则日期将为total>1或value=-1和total=1.使用此方法，如果将value和total相乘，则范围的开始是value*total=1时，范围的结束是value*total=0时，其他任何值都表示在范围中间的日期. /p>
这是什么:
SELECT dt,
CASE SUM( value ) OVER ( ORDER BY dt ASC, value DESC, ROWNUM ) * value
WHEN 1 THEN 'start'
WHEN 0 THEN 'end'
END AS start_end
FROM your_table
UNPIVOT ( dt FOR value IN ( start_date AS 1, end_date AS -1 ) )
然后您可以过滤出start_end为NULL的日期，这将为您提供一个交替的start和end行的表，您可以使用LAG来计算天数差异:
SELECT dt - LAG( dt ) OVER ( ORDER BY dt ) + 1 AS days,
start_end
FROM (
SELECT dt,
CASE SUM( value ) OVER ( ORDER BY dt ASC, value DESC, ROWNUM ) * value
WHEN 1 THEN 'start'
WHEN 0 THEN 'end'
END AS start_end
FROM your_table
UNPIVOT ( dt FOR value IN ( start_date AS 1, end_date AS -1 ) )
)
WHERE start_end IS NOT NULL
然后您要做的就是SUM end - start的所有差异；给出上面的查询.
I am arriving at the total number of days a service has been used in a month. (Start_Date and End_Date are - both inclusive)
Sample Data 1:
User Start_Date End_Date
A 01-Jun-2017 30-Jun-2017
B 06-Jun-2017 30-Jun-2017
Ans: Service used days = 30 days.
Sample Data 2:
User Start_Date End_Date
C 06-Jun-2017 10-Jun-2017
D 02-Jun-2017 02-Jun-2017
Ans: Service used days = 6 days.
How do I write a code to find the same, preferable in SQL to PLSQL.
Test Data:
CREATE TABLE your_table ( usr, start_date, end_date ) AS (
SELECT 'A', DATE '2017-06-01', DATE '2017-06-03' FROM DUAL UNION ALL
SELECT 'B', DATE '2017-06-02', DATE '2017-06-04' FROM DUAL UNION ALL -- Overlaps previous
SELECT 'C', DATE '2017-06-06', DATE '2017-06-06' FROM DUAL UNION ALL
SELECT 'D', DATE '2017-06-07', DATE '2017-06-07' FROM DUAL UNION ALL -- Adjacent to previous
SELECT 'E', DATE '2017-06-11', DATE '2017-06-20' FROM DUAL UNION ALL
SELECT 'F', DATE '2017-06-14', DATE '2017-06-15' FROM DUAL UNION ALL -- Within previous
SELECT 'G', DATE '2017-06-22', DATE '2017-06-25' FROM DUAL UNION ALL
SELECT 'H', DATE '2017-06-24', DATE '2017-06-28' FROM DUAL UNION ALL -- Overlaps previous and next
SELECT 'I', DATE '2017-06-27', DATE '2017-06-30' FROM DUAL UNION ALL
SELECT 'J', DATE '2017-06-27', DATE '2017-06-28' FROM DUAL; -- Within H and I
Query:
SELECT SUM( days ) AS total_days
FROM (
SELECT dt - LAG( dt ) OVER ( ORDER BY dt ) + 1 AS days,
start_end
FROM (
SELECT dt,
CASE SUM( value ) OVER ( ORDER BY dt ASC, value DESC, ROWNUM ) * value
WHEN 1 THEN 'start'
WHEN 0 THEN 'end'
END AS start_end
FROM your_table
UNPIVOT ( dt FOR value IN ( start_date AS 1, end_date AS -1 ) )
)
WHERE start_end IS NOT NULL
)
WHERE start_end = 'end';
Output:
TOTAL_DAYS
----------
25
Explanation:
SELECT dt, value
FROM your_table
UNPIVOT ( dt FOR value IN ( start_date AS 1, end_date AS -1 ) )
This will UNPIVOT the table so that the start and end dates are in the same column (dt) and are given a corresponding value of +1 for a start and -1 for an end date.
SELECT dt,
SUM( value ) OVER ( ORDER BY dt ASC, value DESC, ROWNUM ) AS total,
value
FROM your_table
UNPIVOT ( dt FOR value IN ( start_date AS 1, end_date AS -1 ) )
Will give the start and end dates and the cumulative sum of those generated values. The start of a range will always have value=1 and total=1 and the end of a range will always have total=0. If a date is mid-way through a range then it will either have total>1 or value=-1 and total=1. Using this, if you multiply value and total then the start of a range is when value*total=1 and the end of a range is when value*total=0 and any other value indicates a date that is midway through a range.
Which is what this gives:
SELECT dt,
CASE SUM( value ) OVER ( ORDER BY dt ASC, value DESC, ROWNUM ) * value
WHEN 1 THEN 'start'
WHEN 0 THEN 'end'
END AS start_end
FROM your_table
UNPIVOT ( dt FOR value IN ( start_date AS 1, end_date AS -1 ) )
You can then filter out the dates when the start_end is NULL which will leave you with a table with alternating start and end rows which you can use LAG to calculate the number of days difference:
SELECT dt - LAG( dt ) OVER ( ORDER BY dt ) + 1 AS days,
start_end
FROM (
SELECT dt,
CASE SUM( value ) OVER ( ORDER BY dt ASC, value DESC, ROWNUM ) * value
WHEN 1 THEN 'start'
WHEN 0 THEN 'end'
END AS start_end
FROM your_table
UNPIVOT ( dt FOR value IN ( start_date AS 1, end_date AS -1 ) )
)
WHERE start_end IS NOT NULL
All you need to do then is to SUM all the differences for the end - start; which gives the query above.
这篇关于如何查找一个月中已使用的天数总数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
