- 385.1K All Categories
- 2.6K Data
- 605 Big Data Appliance
- 2K Data Science
- 452.2K Databases
- 222.4K General Database Discussions
- 32 Multilingual Engine
- 576 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 575 SQLcl
- 4K SQL Developer Data Modeler
- 187.8K SQL & PL/SQL
- 21.5K SQL Developer
- 12 Data Integration
- 12 GoldenGate
- 297.7K Development
- 3 Application Development
- 18 Developer Projects
- 140 Programming Languages
- 294.4K Development Tools
- 118 DevOps
- 3.1K QA/Testing
- 646.5K Java
- 30 Java Learning Subscription
- 37K Database Connectivity
- 185 Java Community Process
- 107 Java 25
- 22.2K Java APIs
- 138.3K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 22 Java Essentials
- 174 Java 8 Questions
- 86K Java Programming
- 82 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 206 Java User Groups
- 587 LiveLabs
- 41 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.6K JHeadstart
- 5.8K Other Languages
- 2.3K Chinese
- 197 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 240 Portuguese
select's MODEL clause to add columns on the resultset, not just new rows?
PIVOT can only pivot one value into its one column.
Also PIVOT is not helpful if for examle we want to pivot and (group by) intersecting ranges of values.
So we're planning to use MODEL clause to overcome this limits.
We have to pivot into columns:
- sum(), count() of data over separate quarters for last 2 years and present each quarter's result into its own set of column;
- sum(), count() of data over separate years for last 2 years and present each years's result into its own set of column;
- sum(), count() of data over separate months for last 3 months and present each month's result into its own set of column.
The list of quarters / years / months in the bucketing is fixed and can be hard-coded in the MODEL clause (i.e. it'll be always to look back 2 years).
I have a working prototype doing above using three separate pivots, but it is very inefficient because each pivot has to pass
our huge dataset again.
We expect MODEL might require just one pass over the dataset.
- Is it possible for MODEL clause to create new columns (ie. we group by month and product category in a subquery, but MODEL should add columns for the above quarter/year/month buckets)?
- Can't think of a best way to define DIMENSION BY and MEASURES so we could create new columns..
Any ideas are highly appreciated.