Sliding Window View Construct for Relational Database Query Language
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing materialized views in relational database query language (RDQL) cannot efficiently store and display aggregated data in a sliding window format, losing historical data and failing to handle aggregate functions based on fixed time slices.
Innovation Solution
The introduction of a sliding window view construct that stores pre-calculated query results and retains previous view results, allowing data to be displayed in a summary format over a fixed time period, with novel syntax for creating, populating, and purging rows based on user-defined intervals and retention criteria.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If existing materialized view constructs are used to save pre-calculated query results, then data storage efficiency is improved, but historical view results are lost and aggregate functions cannot be applied to sliding windows
Solution Approach 1:
The patent segments the materialized view into multiple time-based partitions, where each partition stores results for a specific time window. This allows historical results to be preserved in separate partitions while maintaining storage efficiency through selective retention and automatic cleanup of expired partitions.
Solution Approach 2:
The patent adds a time dimension to the traditional materialized view by introducing time-based partitioning and retention policies. This transforms the view from a single static snapshot to a multi-temporal structure that can store and query historical aggregates across different time windows.
2Speed
If existing materialized views store aggregated data, then query performance is improved, but the data cannot be displayed in sliding window format with fixed time slices
Solution Approach 1:
The patent introduces dynamic time window parameters that allow the sliding window size and position to be adjusted without regenerating the entire materialized view. The view structure adapts to different time slice configurations while maintaining pre-calculated aggregates for efficient querying.
Solution Approach 2:
The patent creates a universal materialized view structure that can serve multiple purposes: traditional aggregate queries, sliding window displays with fixed time slices, and historical data retrieval. The same underlying structure supports various query patterns through time-based partitioning and flexible retrieval mechanisms.
3Quantity of substance
If materialized views retain all historical data, then data completeness is improved, but storage requirements and data management complexity increase
Solution Approach 1:
The patent implements periodic retention policies that automatically manage the lifecycle of historical data in the materialized view. Data is retained for specified time periods and then automatically purged or archived, providing structured data completeness while preventing unbounded growth and managing complexity through systematic, time-based operations.
Data Source
AI summary
This disclosure relates to populating a sliding window view comprising one or more rows associated with one or more view intervals. In some implementations of this disclosure, the sliding window view can store the history of previous view results and each row in the sliding window view can correspond to a fixed interval of time. In some implementations, the sliding window view can store a constant number of view results by purging existing view results within the sliding window view. The sliding window view can provide a user with a convenient and efficient means for displaying data summary tables corresponding to a period of time.