Are you over 18 and want to see adult content?
4
More Annotations
3
1
Favourite Annotations
6
![A complete backup of durhamcollege.ca](https://www.archivebay.com/archive2/26936d32-5a0e-47db-ac0f-9067fd3cc8b7.png)
A complete backup of durhamcollege.ca
durhamcollege.ca
Are you over 18 and want to see adult content?
![A complete backup of sohosandiego.org](https://www.archivebay.com/archive2/9e296e7f-4667-4b87-a736-c8c3bc1ebcc7.png)
A complete backup of sohosandiego.org
sohosandiego.org
Are you over 18 and want to see adult content?
![A complete backup of lockpickshop.com](https://www.archivebay.com/archive2/9347d0c9-1b9f-4b9a-9710-13f1cb4ab857.png)
A complete backup of lockpickshop.com
lockpickshop.com
Are you over 18 and want to see adult content?
6
Text
SQL AND MODULARITY: PATTERNS, ANTI-PATTERNS AND THE There’s a lot of importance placed on code re-use in the database development world. In traditional procedural programming languages, such as C or Fortran, the value of modular programming and its application to promoting code re-use is well known and understood. PL/SQL PROFILING 2: HIERARCHICAL PROFILER PL/SQL Profiling 2: Hierarchical Profiler. This article describes the use of Oracle’s hierarchical PL/SQL profiler (DBMS_HProf) on two example program structures. The examples are designed to illustrate its behaviour over as many different scenarios as possible, while keeping the examples as simple as possible. BENCHMARKING ORACLE DML: A CASE STUDY I Deep Slice Graphs. In the deep slice there are 100 dates per product and from 100,000 to 1,000,000 products, with a maximum of 100 million
records.
SQL FOR THE TRAVELLING SALESMAN PROBLEM SQL for the Travelling Salesman Problem. ‘The travelling salesman problem (TSP) or travelling salesperson problem asks the following question: Given a list of cities and the distances between each pair of cities, what is the shortest possible route that visits each city exactly once and returns to the origin city? It is an NP-hard problemin
SQL FOR SHORTEST PATH PROBLEMS I have used similar techniques in the past for other combinatorial problems, including these more complex examples: SQL for the Fantasy Football Knapsack Problem (June 2013), and SQL for the Travelling Salesman Problem (July 2013). 4 May 2015 Update I have just posted SQL for Shortest Path Problems 2: A Branch and Bound Approach. DESIGN PATTERNS FOR DATABASE REPORTS WITH XML PUBLISHER In a recent article, A Design Pattern for Oracle eBusiness Audit Trail Reports with XML Publisher, I presented a report that I developed within Oracle eBusiness 12.1, using Oracle XML Publisher.The report was for displaying audit trail data for a particular table, and I proposed that it could be used as a design pattern for audit trail reporting on any eBusiness table. CASE EXPRESSIONS AND IGNORING NULLS IN ANALYTIC FUNCTIONS It is interesting to note that some of the other functions, such as Sum, ignore nulls implicitly: SELECT 1 + NULL added, Sum (x) summed FROM ( SELECT 1 X FROM DUAL UNION SELECT NULL FROM DUAL); ADDED SUMMED ---------- ---------- 1. In Oracle null signifies an unknown value and therefore adding null to any number, for example, results in null A DESIGN PATTERN FOR ORACLE EBUSINESS AUDIT TRAIL REPORTS Oracle eBusiness applications allow audit history records to be automatically maintained on database tables, as explained in the release 12 System Administrator’s guide, Reporting On AuditTrail Data. Oracle E-Business Suite provides an auditing mechanism based on Oracle database triggers.XML PUBLISHER
Design Patterns for Database Reports with XML Publisher and Email Bursting. Posted on September 20, 2014. by BrendanP. Reply. In a recent article, A Design Pattern for Oracle eBusiness Audit Trail Reports with XML Publisher, I presented a report that I developedwithin
SQL AND MODULARITY: PATTERNS, ANTI-PATTERNS AND THE There’s a lot of importance placed on code re-use in the database development world. In traditional procedural programming languages, such as C or Fortran, the value of modular programming and its application to promoting code re-use is well known and understood. PL/SQL PROFILING 2: HIERARCHICAL PROFILER PL/SQL Profiling 2: Hierarchical Profiler. This article describes the use of Oracle’s hierarchical PL/SQL profiler (DBMS_HProf) on two example program structures. The examples are designed to illustrate its behaviour over as many different scenarios as possible, while keeping the examples as simple as possible. BENCHMARKING ORACLE DML: A CASE STUDY I Deep Slice Graphs. In the deep slice there are 100 dates per product and from 100,000 to 1,000,000 products, with a maximum of 100 millionrecords.
SQL FOR THE TRAVELLING SALESMAN PROBLEM SQL for the Travelling Salesman Problem. ‘The travelling salesman problem (TSP) or travelling salesperson problem asks the following question: Given a list of cities and the distances between each pair of cities, what is the shortest possible route that visits each city exactly once and returns to the origin city? It is an NP-hard problemin
SQL FOR SHORTEST PATH PROBLEMS I have used similar techniques in the past for other combinatorial problems, including these more complex examples: SQL for the Fantasy Football Knapsack Problem (June 2013), and SQL for the Travelling Salesman Problem (July 2013). 4 May 2015 Update I have just posted SQL for Shortest Path Problems 2: A Branch and Bound Approach. DESIGN PATTERNS FOR DATABASE REPORTS WITH XML PUBLISHER In a recent article, A Design Pattern for Oracle eBusiness Audit Trail Reports with XML Publisher, I presented a report that I developed within Oracle eBusiness 12.1, using Oracle XML Publisher.The report was for displaying audit trail data for a particular table, and I proposed that it could be used as a design pattern for audit trail reporting on any eBusiness table. CASE EXPRESSIONS AND IGNORING NULLS IN ANALYTIC FUNCTIONS It is interesting to note that some of the other functions, such as Sum, ignore nulls implicitly: SELECT 1 + NULL added, Sum (x) summed FROM ( SELECT 1 X FROM DUAL UNION SELECT NULL FROM DUAL); ADDED SUMMED ---------- ---------- 1. In Oracle null signifies an unknown value and therefore adding null to any number, for example, results in null A DESIGN PATTERN FOR ORACLE EBUSINESS AUDIT TRAIL REPORTS Oracle eBusiness applications allow audit history records to be automatically maintained on database tables, as explained in the release 12 System Administrator’s guide, Reporting On AuditTrail Data. Oracle E-Business Suite provides an auditing mechanism based on Oracle database triggers.XML PUBLISHER
Design Patterns for Database Reports with XML Publisher and Email Bursting. Posted on September 20, 2014. by BrendanP. Reply. In a recent article, A Design Pattern for Oracle eBusiness Audit Trail Reports with XML Publisher, I presented a report that I developedwithin
DESIGN PATTERNS FOR DATABASE REPORTS WITH XML PUBLISHER In a recent article, A Design Pattern for Oracle eBusiness Audit Trail Reports with XML Publisher, I presented a report that I developed within Oracle eBusiness 12.1, using Oracle XML Publisher.The report was for displaying audit trail data for a particular table, and I proposed that it could be used as a design pattern for audit trail reporting on any eBusiness table. A SIMPLE SQL SOLUTION FOR THE KNAPSACK PROBLEM (SKP-1) A poster on OTN ( Combination using pl/sql) recently asked for an SQL solution to a problem that turned out to be an example of the well known Knapsack Problem, for the case of a single knapsack. I posted an SQL query as a solution, and also a solution in PL/SQL because the SQL solution uses a feature only available in Oracle v11.2. DIMENSIONAL BENCHMARKING OF STRING SPLITTING SQL I noticed a question on AskTom last November concerning SQL for splitting delimited strings, Extract domain names from a column having multiple email addresses, a kind of question that arises frequently on the forums.There was some debate between reviewers Rajeshwaran Jeyabal, Stew Ashton and the AskTom guys on whether an XML-based solution performs better or worse than aXML PUBLISHER
In a recent article, A Design Pattern for Oracle eBusiness Audit Trail Reports with XML Publisher, I presented a report that I developed within Oracle eBusiness 12.1, using Oracle XML Publisher.The report was for displaying audit trail data for a particular table, and I proposed that it could be used as a design pattern for audit trail reporting on any eBusiness table. XDOLOADER | A PROGRAMMER WRITES… (BRENDAN'S BLOG) XDOLoader uploads – uploading the data and any bursting XML files, and all layout templates present. SQL installations – installing any SQL script present, and the package spec and body files. The following table gives a few notes on the main program and preliminary subroutines. Preliminaries Summary. The following table gives a fewnotes
SQL AND MODULARITY: PATTERNS, ANTI-PATTERNS AND THE There’s a lot of importance placed on code re-use in the database development world. In traditional procedural programming languages, such as C or Fortran, the value of modular programming and its application to promoting code re-use is well known and understood. PL/SQL PROFILING 2: HIERARCHICAL PROFILER PL/SQL Profiling 2: Hierarchical Profiler. This article describes the use of Oracle’s hierarchical PL/SQL profiler (DBMS_HProf) on two example program structures. The examples are designed to illustrate its behaviour over as many different scenarios as possible, while keeping the examples as simple as possible. BENCHMARKING ORACLE DML: A CASE STUDY I Deep Slice Graphs. In the deep slice there are 100 dates per product and from 100,000 to 1,000,000 products, with a maximum of 100 millionrecords.
SQL FOR THE TRAVELLING SALESMAN PROBLEM SQL for the Travelling Salesman Problem. ‘The travelling salesman problem (TSP) or travelling salesperson problem asks the following question: Given a list of cities and the distances between each pair of cities, what is the shortest possible route that visits each city exactly once and returns to the origin city? It is an NP-hard problemin
SQL FOR SHORTEST PATH PROBLEMS I have used similar techniques in the past for other combinatorial problems, including these more complex examples: SQL for the Fantasy Football Knapsack Problem (June 2013), and SQL for the Travelling Salesman Problem (July 2013). 4 May 2015 Update I have just posted SQL for Shortest Path Problems 2: A Branch and Bound Approach. DESIGN PATTERNS FOR DATABASE REPORTS WITH XML PUBLISHER In a recent article, A Design Pattern for Oracle eBusiness Audit Trail Reports with XML Publisher, I presented a report that I developed within Oracle eBusiness 12.1, using Oracle XML Publisher.The report was for displaying audit trail data for a particular table, and I proposed that it could be used as a design pattern for audit trail reporting on any eBusiness table. CASE EXPRESSIONS AND IGNORING NULLS IN ANALYTIC FUNCTIONS It is interesting to note that some of the other functions, such as Sum, ignore nulls implicitly: SELECT 1 + NULL added, Sum (x) summed FROM ( SELECT 1 X FROM DUAL UNION SELECT NULL FROM DUAL); ADDED SUMMED ---------- ---------- 1. In Oracle null signifies an unknown value and therefore adding null to any number, for example, results in null A DESIGN PATTERN FOR ORACLE EBUSINESS AUDIT TRAIL REPORTS Oracle eBusiness applications allow audit history records to be automatically maintained on database tables, as explained in the release 12 System Administrator’s guide, Reporting On AuditTrail Data. Oracle E-Business Suite provides an auditing mechanism based on Oracle database triggers.XML PUBLISHER
Design Patterns for Database Reports with XML Publisher and Email Bursting. Posted on September 20, 2014. by BrendanP. Reply. In a recent article, A Design Pattern for Oracle eBusiness Audit Trail Reports with XML Publisher, I presented a report that I developedwithin
SQL AND MODULARITY: PATTERNS, ANTI-PATTERNS AND THE There’s a lot of importance placed on code re-use in the database development world. In traditional procedural programming languages, such as C or Fortran, the value of modular programming and its application to promoting code re-use is well known and understood. PL/SQL PROFILING 2: HIERARCHICAL PROFILER PL/SQL Profiling 2: Hierarchical Profiler. This article describes the use of Oracle’s hierarchical PL/SQL profiler (DBMS_HProf) on two example program structures. The examples are designed to illustrate its behaviour over as many different scenarios as possible, while keeping the examples as simple as possible. BENCHMARKING ORACLE DML: A CASE STUDY I Deep Slice Graphs. In the deep slice there are 100 dates per product and from 100,000 to 1,000,000 products, with a maximum of 100 millionrecords.
SQL FOR THE TRAVELLING SALESMAN PROBLEM SQL for the Travelling Salesman Problem. ‘The travelling salesman problem (TSP) or travelling salesperson problem asks the following question: Given a list of cities and the distances between each pair of cities, what is the shortest possible route that visits each city exactly once and returns to the origin city? It is an NP-hard problemin
SQL FOR SHORTEST PATH PROBLEMS I have used similar techniques in the past for other combinatorial problems, including these more complex examples: SQL for the Fantasy Football Knapsack Problem (June 2013), and SQL for the Travelling Salesman Problem (July 2013). 4 May 2015 Update I have just posted SQL for Shortest Path Problems 2: A Branch and Bound Approach. DESIGN PATTERNS FOR DATABASE REPORTS WITH XML PUBLISHER In a recent article, A Design Pattern for Oracle eBusiness Audit Trail Reports with XML Publisher, I presented a report that I developed within Oracle eBusiness 12.1, using Oracle XML Publisher.The report was for displaying audit trail data for a particular table, and I proposed that it could be used as a design pattern for audit trail reporting on any eBusiness table. CASE EXPRESSIONS AND IGNORING NULLS IN ANALYTIC FUNCTIONS It is interesting to note that some of the other functions, such as Sum, ignore nulls implicitly: SELECT 1 + NULL added, Sum (x) summed FROM ( SELECT 1 X FROM DUAL UNION SELECT NULL FROM DUAL); ADDED SUMMED ---------- ---------- 1. In Oracle null signifies an unknown value and therefore adding null to any number, for example, results in null A DESIGN PATTERN FOR ORACLE EBUSINESS AUDIT TRAIL REPORTS Oracle eBusiness applications allow audit history records to be automatically maintained on database tables, as explained in the release 12 System Administrator’s guide, Reporting On AuditTrail Data. Oracle E-Business Suite provides an auditing mechanism based on Oracle database triggers.XML PUBLISHER
Design Patterns for Database Reports with XML Publisher and Email Bursting. Posted on September 20, 2014. by BrendanP. Reply. In a recent article, A Design Pattern for Oracle eBusiness Audit Trail Reports with XML Publisher, I presented a report that I developedwithin
DESIGN PATTERNS FOR DATABASE REPORTS WITH XML PUBLISHER In a recent article, A Design Pattern for Oracle eBusiness Audit Trail Reports with XML Publisher, I presented a report that I developed within Oracle eBusiness 12.1, using Oracle XML Publisher.The report was for displaying audit trail data for a particular table, and I proposed that it could be used as a design pattern for audit trail reporting on any eBusiness table. A SIMPLE SQL SOLUTION FOR THE KNAPSACK PROBLEM (SKP-1) A poster on OTN ( Combination using pl/sql) recently asked for an SQL solution to a problem that turned out to be an example of the well known Knapsack Problem, for the case of a single knapsack. I posted an SQL query as a solution, and also a solution in PL/SQL because the SQL solution uses a feature only available in Oracle v11.2. DIMENSIONAL BENCHMARKING OF STRING SPLITTING SQL I noticed a question on AskTom last November concerning SQL for splitting delimited strings, Extract domain names from a column having multiple email addresses, a kind of question that arises frequently on the forums.There was some debate between reviewers Rajeshwaran Jeyabal, Stew Ashton and the AskTom guys on whether an XML-based solution performs better or worse than aXML PUBLISHER
In a recent article, A Design Pattern for Oracle eBusiness Audit Trail Reports with XML Publisher, I presented a report that I developed within Oracle eBusiness 12.1, using Oracle XML Publisher.The report was for displaying audit trail data for a particular table, and I proposed that it could be used as a design pattern for audit trail reporting on any eBusiness table. XDOLOADER | A PROGRAMMER WRITES… (BRENDAN'S BLOG) XDOLoader uploads – uploading the data and any bursting XML files, and all layout templates present. SQL installations – installing any SQL script present, and the package spec and body files. The following table gives a few notes on the main program and preliminary subroutines. Preliminaries Summary. The following table gives a fewnotes
SQL AND MODULARITY: PATTERNS, ANTI-PATTERNS AND THE There’s a lot of importance placed on code re-use in the database development world. In traditional procedural programming languages, such as C or Fortran, the value of modular programming and its application to promoting code re-use is well known and understood. PL/SQL PROFILING 2: HIERARCHICAL PROFILER PL/SQL Profiling 2: Hierarchical Profiler. This article describes the use of Oracle’s hierarchical PL/SQL profiler (DBMS_HProf) on two example program structures. The examples are designed to illustrate its behaviour over as many different scenarios as possible, while keeping the examples as simple as possible. SQL FOR THE TRAVELLING SALESMAN PROBLEM SQL for the Travelling Salesman Problem. ‘The travelling salesman problem (TSP) or travelling salesperson problem asks the following question: Given a list of cities and the distances between each pair of cities, what is the shortest possible route that visits each city exactly once and returns to the origin city? It is an NP-hard problemin
BENCHMARKING ORACLE DML: A CASE STUDY I Deep Slice Graphs. In the deep slice there are 100 dates per product and from 100,000 to 1,000,000 products, with a maximum of 100 millionrecords.
DESIGN PATTERNS FOR DATABASE REPORTS WITH XML PUBLISHER In a recent article, A Design Pattern for Oracle eBusiness Audit Trail Reports with XML Publisher, I presented a report that I developed within Oracle eBusiness 12.1, using Oracle XML Publisher.The report was for displaying audit trail data for a particular table, and I proposed that it could be used as a design pattern for audit trail reporting on any eBusiness table. SQL FOR SHORTEST PATH PROBLEMS I have used similar techniques in the past for other combinatorial problems, including these more complex examples: SQL for the Fantasy Football Knapsack Problem (June 2013), and SQL for the Travelling Salesman Problem (July 2013). 4 May 2015 Update I have just posted SQL for Shortest Path Problems 2: A Branch and Bound Approach. CASE EXPRESSIONS AND IGNORING NULLS IN ANALYTIC FUNCTIONS It is interesting to note that some of the other functions, such as Sum, ignore nulls implicitly: SELECT 1 + NULL added, Sum (x) summed FROM ( SELECT 1 X FROM DUAL UNION SELECT NULL FROM DUAL); ADDED SUMMED ---------- ---------- 1. In Oracle null signifies an unknown value and therefore adding null to any number, for example, results in null A DESIGN PATTERN FOR ORACLE EBUSINESS AUDIT TRAIL REPORTS Oracle eBusiness applications allow audit history records to be automatically maintained on database tables, as explained in the release 12 System Administrator’s guide, Reporting On AuditTrail Data. Oracle E-Business Suite provides an auditing mechanism based on Oracle database triggers. SQL AND MODULARITY: PATTERNS, ANTI-PATTERNS AND THE There’s a lot of importance placed on code re-use in the database development world. In traditional procedural programming languages, such as C or Fortran, the value of modular programming and its application to promoting code re-use is well known and understood. PL/SQL PROFILING 2: HIERARCHICAL PROFILER PL/SQL Profiling 2: Hierarchical Profiler. This article describes the use of Oracle’s hierarchical PL/SQL profiler (DBMS_HProf) on two example program structures. The examples are designed to illustrate its behaviour over as many different scenarios as possible, while keeping the examples as simple as possible. SQL FOR THE TRAVELLING SALESMAN PROBLEM SQL for the Travelling Salesman Problem. ‘The travelling salesman problem (TSP) or travelling salesperson problem asks the following question: Given a list of cities and the distances between each pair of cities, what is the shortest possible route that visits each city exactly once and returns to the origin city? It is an NP-hard problemin
BENCHMARKING ORACLE DML: A CASE STUDY I Deep Slice Graphs. In the deep slice there are 100 dates per product and from 100,000 to 1,000,000 products, with a maximum of 100 millionrecords.
DESIGN PATTERNS FOR DATABASE REPORTS WITH XML PUBLISHER In a recent article, A Design Pattern for Oracle eBusiness Audit Trail Reports with XML Publisher, I presented a report that I developed within Oracle eBusiness 12.1, using Oracle XML Publisher.The report was for displaying audit trail data for a particular table, and I proposed that it could be used as a design pattern for audit trail reporting on any eBusiness table. SQL FOR SHORTEST PATH PROBLEMS I have used similar techniques in the past for other combinatorial problems, including these more complex examples: SQL for the Fantasy Football Knapsack Problem (June 2013), and SQL for the Travelling Salesman Problem (July 2013). 4 May 2015 Update I have just posted SQL for Shortest Path Problems 2: A Branch and Bound Approach. CASE EXPRESSIONS AND IGNORING NULLS IN ANALYTIC FUNCTIONS It is interesting to note that some of the other functions, such as Sum, ignore nulls implicitly: SELECT 1 + NULL added, Sum (x) summed FROM ( SELECT 1 X FROM DUAL UNION SELECT NULL FROM DUAL); ADDED SUMMED ---------- ---------- 1. In Oracle null signifies an unknown value and therefore adding null to any number, for example, results in null A DESIGN PATTERN FOR ORACLE EBUSINESS AUDIT TRAIL REPORTS Oracle eBusiness applications allow audit history records to be automatically maintained on database tables, as explained in the release 12 System Administrator’s guide, Reporting On AuditTrail Data. Oracle E-Business Suite provides an auditing mechanism based on Oracle database triggers. DESIGN PATTERNS FOR DATABASE REPORTS WITH XML PUBLISHER In a recent article, A Design Pattern for Oracle eBusiness Audit Trail Reports with XML Publisher, I presented a report that I developed within Oracle eBusiness 12.1, using Oracle XML Publisher.The report was for displaying audit trail data for a particular table, and I proposed that it could be used as a design pattern for audit trail reporting on any eBusiness table. SQL FOR THE BALANCED NUMBER PARTITIONING PROBLEM I noticed a post on AskTom recently that referred to an SQL solution to a version of the so-called Bin Fitting problem, where even distribution is the aim. The solution, How do I solve a Bin Fitting problem in an SQL statement?, uses Oracle’s Model clause, and, as the poster of the link observed, has the drawback that the number of bins is embedded in the query structure. A PROGRAMMER WRITES… (BRENDAN'S BLOG) WITH depsals AS ( SELECT dep.department_id, dep.manager_id, Avg(emp.salary) avgsal FROM departments dep JOIN employees emp ON emp.department_id = dep.department_id GROUP BY Dep.department_id, dep.manager_id ) SELECT emp.employee_id, emp.salary, dsl.avgsal, Round(Nvl(dsl.avgsal, emp.salary) * 0.1 * Nvl2(jhs.employee_id, 1.1, 1) * CASE job.job_id WHEN XDOLOADER | A PROGRAMMER WRITES… (BRENDAN'S BLOG) XDOLoader uploads – uploading the data and any bursting XML files, and all layout templates present. SQL installations – installing any SQL script present, and the package spec and body files. The following table gives a few notes on the main program and preliminary subroutines. Preliminaries Summary. The following table gives a fewnotes
SQL AND MODULARITY: PATTERNS, ANTI-PATTERNS AND THE There’s a lot of importance placed on code re-use in the database development world. In traditional procedural programming languages, such as C or Fortran, the value of modular programming and its application to promoting code re-use is well known and understood. CASE EXPRESSIONS AND IGNORING NULLS IN ANALYTIC FUNCTIONS It is interesting to note that some of the other functions, such as Sum, ignore nulls implicitly: SELECT 1 + NULL added, Sum (x) summed FROM ( SELECT 1 X FROM DUAL UNION SELECT NULL FROM DUAL); ADDED SUMMED ---------- ---------- 1. In Oracle null signifies an unknown value and therefore adding null to any number, for example, results in null PL/SQL PROFILING 2: HIERARCHICAL PROFILER PL/SQL Profiling 2: Hierarchical Profiler. This article describes the use of Oracle’s hierarchical PL/SQL profiler (DBMS_HProf) on two example program structures. The examples are designed to illustrate its behaviour over as many different scenarios as possible, while keeping the examples as simple as possible. DESIGN PATTERNS FOR DATABASE REPORTS WITH XML PUBLISHERORACLE XML PUBLISHERORACLE XML PUBLISHER REPORTSORACLE XML PUBLISHER TUTORIALXML PUBLISHER ADMINISTRATORXML PUBLISHER DOWNLOAD In a recent article, A Design Pattern for Oracle eBusiness Audit Trail Reports with XML Publisher, I presented a report that I developed within Oracle eBusiness 12.1, using Oracle XML Publisher.The report was for displaying audit trail data for a particular table, and I proposed that it could be used as a design pattern for audit trail reporting on any eBusiness table. AN SQL SOLUTION FOR THE MULTIPLE KNAPSACK PROBLEM (SKP-M In my last article, A Simple SQL Solution for the Knapsack Problem (SKP-1), I presented an SQL solution for the well known knapsack problem in its simpler 1-knapsack form (and it is advisable to read the first article before this one).Here I present an SQL solution for the problem in its more difficult multiple-knapsack form. The solution is a modified version of one I posted on OTN, SQL Query BENCHMARKING ORACLE DML: A CASE STUDY I Deep Slice Graphs. In the deep slice there are 100 dates per product and from 100,000 to 1,000,000 products, with a maximum of 100 millionrecords.
SQL FOR SHORTEST PATH PROBLEMS I have used similar techniques in the past for other combinatorial problems, including these more complex examples: SQL for the Fantasy Football Knapsack Problem (June 2013), and SQL for the Travelling Salesman Problem (July 2013). 4 May 2015 Update I have just posted SQL for Shortest Path Problems 2: A Branch and Bound Approach. A NOTE ON ORACLE JOIN ORDERS AND HINTS Inner-level join order. This is simply the side on which a table is joined to a rowset in a given join, say 1 for the left side, and 2 for the right side in a hash join. Note that the leading hint treats both levels of join order, but behaves differently between hash joins and LIST AGGREGATION IN ORACLE List Aggregation in Oracle – Comparing Three Methods. In my last article, Grouping by Unique Subsequences in Oracle, I compared three solutions to a querying problem in Oracle. I found that a solution using a pipelined function was fastest across a range of test data sets, while another using Oracle’s Model clause turned out to beextremely
A SIMPLE SQL SOLUTION FOR THE KNAPSACK PROBLEM (SKP-1) A poster on OTN ( Combination using pl/sql) recently asked for an SQL solution to a problem that turned out to be an example of the well known Knapsack Problem, for the case of a single knapsack. I posted an SQL query as a solution, and also a solution in PL/SQL because the SQL solution uses a feature only available in Oracle v11.2. SQL AND MODULARITY: PATTERNS, ANTI-PATTERNS AND THE There’s a lot of importance placed on code re-use in the database development world. In traditional procedural programming languages, such as C or Fortran, the value of modular programming and its application to promoting code re-use is well known and understood. CASE EXPRESSIONS AND IGNORING NULLS IN ANALYTIC FUNCTIONS It is interesting to note that some of the other functions, such as Sum, ignore nulls implicitly: SELECT 1 + NULL added, Sum (x) summed FROM ( SELECT 1 X FROM DUAL UNION SELECT NULL FROM DUAL); ADDED SUMMED ---------- ---------- 1. In Oracle null signifies an unknown value and therefore adding null to any number, for example, results in null PL/SQL PROFILING 2: HIERARCHICAL PROFILER PL/SQL Profiling 2: Hierarchical Profiler. This article describes the use of Oracle’s hierarchical PL/SQL profiler (DBMS_HProf) on two example program structures. The examples are designed to illustrate its behaviour over as many different scenarios as possible, while keeping the examples as simple as possible. DESIGN PATTERNS FOR DATABASE REPORTS WITH XML PUBLISHERORACLE XML PUBLISHERORACLE XML PUBLISHER REPORTSORACLE XML PUBLISHER TUTORIALXML PUBLISHER ADMINISTRATORXML PUBLISHER DOWNLOAD In a recent article, A Design Pattern for Oracle eBusiness Audit Trail Reports with XML Publisher, I presented a report that I developed within Oracle eBusiness 12.1, using Oracle XML Publisher.The report was for displaying audit trail data for a particular table, and I proposed that it could be used as a design pattern for audit trail reporting on any eBusiness table. AN SQL SOLUTION FOR THE MULTIPLE KNAPSACK PROBLEM (SKP-M In my last article, A Simple SQL Solution for the Knapsack Problem (SKP-1), I presented an SQL solution for the well known knapsack problem in its simpler 1-knapsack form (and it is advisable to read the first article before this one).Here I present an SQL solution for the problem in its more difficult multiple-knapsack form. The solution is a modified version of one I posted on OTN, SQL Query BENCHMARKING ORACLE DML: A CASE STUDY I Deep Slice Graphs. In the deep slice there are 100 dates per product and from 100,000 to 1,000,000 products, with a maximum of 100 millionrecords.
SQL FOR SHORTEST PATH PROBLEMS I have used similar techniques in the past for other combinatorial problems, including these more complex examples: SQL for the Fantasy Football Knapsack Problem (June 2013), and SQL for the Travelling Salesman Problem (July 2013). 4 May 2015 Update I have just posted SQL for Shortest Path Problems 2: A Branch and Bound Approach. A NOTE ON ORACLE JOIN ORDERS AND HINTS Inner-level join order. This is simply the side on which a table is joined to a rowset in a given join, say 1 for the left side, and 2 for the right side in a hash join. Note that the leading hint treats both levels of join order, but behaves differently between hash joins and LIST AGGREGATION IN ORACLE List Aggregation in Oracle – Comparing Three Methods. In my last article, Grouping by Unique Subsequences in Oracle, I compared three solutions to a querying problem in Oracle. I found that a solution using a pipelined function was fastest across a range of test data sets, while another using Oracle’s Model clause turned out to beextremely
A SIMPLE SQL SOLUTION FOR THE KNAPSACK PROBLEM (SKP-1) A poster on OTN ( Combination using pl/sql) recently asked for an SQL solution to a problem that turned out to be an example of the well known Knapsack Problem, for the case of a single knapsack. I posted an SQL query as a solution, and also a solution in PL/SQL because the SQL solution uses a feature only available in Oracle v11.2. ABOUT | A PROGRAMMER WRITES… (BRENDAN'S BLOG) About. I’m Brendan and I work as an independent Oracle consultant in Europe. I like this poem: “Hope” is the thing with feathers – (314) BY EMILY DICKINSON. “Hope” is the thing with feathers –. That perches in the soul –. And sings the tune without the words–. And
A NOTE ON ORACLE JOIN ORDERS AND HINTS Inner-level join order. This is simply the side on which a table is joined to a rowset in a given join, say 1 for the left side, and 2 for the right side in a hash join. Note that the leading hint treats both levels of join order, but behaves differently between hash joins and ORACLE AND JUNIT DATA DRIVEN TESTING: AN EXAMPLE Oracle and JUnit Data Driven Testing: An Example. Posted on November 13, 2016. by BrendanP. In Design Patterns for Database API Testing 1: Web Service Saving 2 – Code I identified a number of antipatterns commonly seen in database testing. utPLSQL, originally developed by Steve Feuerstein around 15 years ago, may have been the first OracleXML PUBLISHER
Design Patterns for Database Reports with XML Publisher and Email Bursting. Posted on September 20, 2014. by BrendanP. Reply. In a recent article, A Design Pattern for Oracle eBusiness Audit Trail Reports with XML Publisher, I presented a report that I developed within Oracle eBusiness 12.1, using Oracle XML Publisher. ORACLE PL/SQL API DEMOS GITHUB MODULE This post is essentially the readme for my Github module, Oracle PL/SQL API Demos This is a Github module demonstrating instrumentationand
WALKING TO WORK
Walking to Work. I like to walk to work when I can and I don’t mind going a fair distance as it’s a good way to maintain a base fitness. I also find it a good time to think about technical stuff away from the computer. This odyssey represents my journey to IFSC via Phoenix Park and the Quays, from Auburn Avenue roundabout, for a three month LIST AGGREGATION IN ORACLE List Aggregation in Oracle – Comparing Three Methods. In my last article, Grouping by Unique Subsequences in Oracle, I compared three solutions to a querying problem in Oracle. I found that a solution using a pipelined function was fastest across a range of test data sets, while another using Oracle’s Model clause turned out to beextremely
A FRAMEWORK FOR DIMENSIONAL BENCHMARKING OF SQL QUERY A few years ago I wanted to investigate the performance of different SQL queries for the same problem, and wanted to include analysis ofhow the
MASTER-DETAIL TRANSACTION RECONCILIATION IN SQL (MDTM3 This is the final article in a sequence of three on the subject of master-detail transaction matching. In the first article, Master-Detail Transaction Matching in SQL (MDTM1), I described the problem and divided it into two subproblems, the first being to identify all pairs of matching transactions and the second being to reconcile the pairs so that one transaction matches against at mostone
SQL FOR THE BALANCED NUMBER PARTITIONING PROBLEM I noticed a post on AskTom recently that referred to an SQL solution to a version of the so-called Bin Fitting problem, where even distribution is the aim. The solution, How do I solve a Bin Fitting problem in an SQL statement?, uses Oracle’s Model clause, and, as the poster of the link observed, has the drawback that the number of bins is embedded in the query structure. A PROGRAMMER WRITES… (BRENDAN'S BLOG)Search
MAIN MENU
Skip to primary content Skip to secondary content* Home
* About
* Scribd Articles
* Walking to Work
POST NAVIGATION
← Older posts
CREATING OPEN SOURCE PROJECTS FOR DATABASE CODE – IRELAND ORACLE USER GROUP CONFERENCE 2020 Posted on February 23, 2020 byBrendanP
Reply
I’m speaking on ‘Creating Open Source Projects for Database Code’ at the Oracle User Group Ireland conference 2020 at 4pm on 12 March 2020 in the Gresham hotel, Dublin. Here’s my abstract: and here’s the agenda . Here is a short Twitter thread with a couple of recordings with quick tips on GitHub READMEs: GitHub README tips.
I made a Twitter thread of recordings on my presentation at the 2019 conference: Writing Clean Code in PL/SQL and SQL – TwitterRecordings
#oug_ire #OUGIreland20Share24
Posted in Uncategorized | Tagged GitHub , markdown, md
, Oracle
, PL/SQL
, README
| Leave a Reply
ANALYTIC AND RECURSIVE SQL BY EXAMPLE Posted on February 14, 2020 byBrendanP
Reply
In my article A Note on Running Sums and Products in SQL , I used three different SQL techniques to get running products: Analytic Functions, Model Clause and Recursive Subquery Factors. I explained this in a recording onTwitter . I
then wondered whether I could explain each of these SQL techniques in general using a single Twitter recording (which has a time limit of 140 seconds) each, and you can see the results in this Twitter thread.
In this article I set out the example queries that I used along with the results. You can get the complete scripts and recordings on GitHub, Oracle SQL Projects.
ANALYTIC FUNCTIONS
Oracle Doc: SQL for Analysis and Reporting SQL Analytic Functions in a TweetAVERAGE BY GROUPING
SELECT department_id, AVG(salary) avg_salaryFROM employees
GROUP BY department_id ORDER BY department_id/
DEPARTMENT_ID AVG_SALARY ------------- ---------- 10 4400 20 9500 30 4150 40 650050 3475.55556
60 576070 10000
80 8955.88235
90 19333.3333
100 8601.33333
110 101547000
ANALYTIC AVERAGES: OVERALL, RUNNING AND 3-POINT MOVING SELECT department_id, employee_id, salary, AVG(salary) OVER (PARTITION BY department_id) avg_salary, AVG(salary) OVER (PARTITION BY department_id ORDER BY employee_id) run_avg_salary, AVG(salary) OVER (PARTITION BY department_id ORDER BY employee_id ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) moving_avg_salary_3FROM employees
ORDER BY department_id, employee_id/
DEPARTMENT_ID EMPLOYEE_ID SALARY AVG_SALARY RUN_AVG_SALARY MOVING_AVG_SALARY_3 ------------- ----------- ---------- ---------- -------------- ------------------- 10 200 4400 4400 4400 4400 20 201 13000 9500 13000 13000 202 6000 9500 9500 9500 30 114 11000 4150 11000 11000 115 3100 4150 7050 7050 116 2900 4150 5666.66667 5666.66667 117 2800 4150 4950 2933.33333 118 2600 4150 4480 2766.66667 119 2500 4150 4150 2633.33333 40 203 6500 6500 6500 6500 50 120 8000 3475.55556 8000 8000 121 8200 3475.55556 8100 8100 122 7900 3475.55556 8033.33333 8033.33333 123 6500 3475.55556 7650 7533.33333 124 5800 3475.55556 7280 6733.33333 125 3200 3475.55556 6600 5166.66667 126 2700 3475.55556 6042.85714 3900 127 2400 3475.55556 5587.5 2766.66667 128 2200 3475.55556 5211.11111 2433.33333 129 3300 3475.55556 5020 2633.33333 130 2800 3475.55556 4818.18182 2766.66667 131 2500 3475.55556 4625 2866.66667 132 2100 3475.55556 4430.76923 2466.66667 133 3300 3475.55556 4350 2633.33333 134 2900 3475.55556 4253.33333 2766.66667 135 2400 3475.55556 4137.5 2866.66667 136 2200 3475.55556 4023.52941 2500 137 3600 3475.55556 4000 2733.33333 138 3200 3475.55556 3957.89474 3000 139 2700 3475.55556 3895 3166.66667 140 2500 3475.55556 3828.57143 2800 141 3500 3475.55556 3813.63636 2900 142 3100 3475.55556 3782.6087 3033.33333 143 2600 3475.55556 3733.33333 3066.66667 144 2500 3475.55556 3684 2733.33333 180 3200 3475.55556 3665.38462 2766.66667 181 3100 3475.55556 3644.44444 2933.33333 182 2500 3475.55556 3603.57143 2933.33333 183 2800 3475.55556 3575.86207 2800 184 4200 3475.55556 3596.66667 3166.66667 185 4100 3475.55556 3612.90323 3700 186 3400 3475.55556 3606.25 3900 187 3000 3475.55556 3587.87879 3500 188 3800 3475.55556 3594.11765 3400 189 3600 3475.55556 3594.28571 3466.66667 190 2900 3475.55556 3575 3433.33333 191 2500 3475.55556 3545.94595 3000 192 4000 3475.55556 3557.89474 3133.33333 193 3900 3475.55556 3566.66667 3466.66667 194 3200 3475.55556 3557.5 3700 195 2800 3475.55556 3539.02439 3300 196 3100 3475.55556 3528.57143 3033.33333 197 3000 3475.55556 3516.27907 2966.66667 198 2600 3475.55556 3495.45455 2900 199 2600 3475.55556 3475.55556 2733.33333 60 103 9000 5760 9000 9000 104 6000 5760 7500 7500 105 4800 5760 6600 6600 106 4800 5760 6150 5200 107 4200 5760 5760 4600 70 204 10000 10000 10000 10000 80 145 14000 8955.88235 14000 14000 146 13500 8955.88235 13750 13750 147 12000 8955.88235 13166.6667 13166.6667 148 11000 8955.88235 12625 12166.6667 149 10500 8955.88235 12200 11166.6667 150 10000 8955.88235 11833.3333 10500 151 9500 8955.88235 11500 10000 152 9000 8955.88235 11187.5 9500 153 8000 8955.88235 10833.3333 8833.33333 154 7500 8955.88235 10500 8166.66667 155 7000 8955.88235 10181.8182 7500 156 10000 8955.88235 10166.6667 8166.66667 157 9500 8955.88235 10115.3846 8833.33333 158 9000 8955.88235 10035.7143 9500 159 8000 8955.88235 9900 8833.33333 160 7500 8955.88235 9750 8166.66667 161 7000 8955.88235 9588.23529 7500 162 10500 8955.88235 9638.88889 8333.33333 163 9500 8955.88235 9631.57895 9000 164 7200 8955.88235 9510 9066.66667 165 6800 8955.88235 9380.95238 7833.33333 166 6400 8955.88235 9245.45455 6800 167 6200 8955.88235 9113.04348 6466.66667 168 11500 8955.88235 9212.5 8033.33333 169 10000 8955.88235 9244 9233.33333 170 9600 8955.88235 9257.69231 10366.6667 171 7400 8955.88235 9188.88889 9000 172 7300 8955.88235 9121.42857 8100 173 6100 8955.88235 9017.24138 6933.33333 174 11000 8955.88235 9083.33333 8133.33333 175 8800 8955.88235 9074.19355 8633.33333 176 8600 8955.88235 9059.375 9466.66667 177 8400 8955.88235 9039.39394 8600 179 6200 8955.88235 8955.88235 7733.33333 90 100 24000 19333.3333 24000 24000 101 17000 19333.3333 20500 20500 102 17000 19333.3333 19333.3333 19333.3333 100 108 12008 8601.33333 12008 12008 109 9000 8601.33333 10504 10504 110 8200 8601.33333 9736 9736 111 7700 8601.33333 9227 8300 112 7800 8601.33333 8941.6 7900 113 6900 8601.33333 8601.33333 7466.66667 110 205 12008 10154 12008 12008 206 8300 10154 10154 10154 178 7000 7000 7000 7000 107 rows selected. ANALYTICS ON GROUPING: RUNNING SUM OF THE DEPARTMENT AVERAGE SALARIES DEPARTMENT_ID AVG_SALARY RUN_SUM_AVG_SALARY ------------- ---------- ------------------ 10 4400 4400 20 9500 13900 30 4150 18050 40 6500 24550 50 3475.55556 28025.5556 60 5760 33785.5556 70 10000 43785.5556 80 8955.88235 52741.4379 90 19333.3333 72074.7712 100 8601.33333 80676.1046 110 10154 90830.1046 7000 97830.1046MODEL CLAUSE
Oracle Doc: SQL for Modeling SQL Model Clause in a Tweet RUNNING AND FINAL PRODUCTS: FINAL FIRST RULE, DEFAULT SEQUENTIAL ORDER WITH multipliers AS ( SELECT department_id, employee_id, salary, (1 + salary/10000) mult, COUNT(*) OVER (PARTITION BY department_id) n_empsFROM employees
)
SELECT department_id, employee_id, salary, mult, running_prod, final_prodFROM multipliers
MODEL
PARTITION BY (department_id) DIMENSION BY (Row_Number() OVER (PARTITION BY department_id ORDER BY employee_id) rn) MEASURES (employee_id, salary, mult, mult running_prod, mult final_prod, n_emps)RULES (
final_prod = running_prod, running_prod = mult * running_prod)
ORDER BY department_id, employee_id/
DEPARTMENT_ID EMPLOYEE_ID SALARY MULT RUNNING_PROD FINAL_PROD ------------- ----------- ---------- ---------- ------------ ---------- 10 200 4400 1.44 1.44 1.44 20 201 13000 2.3 2.3 1.6 202 6000 1.6 3.68 1.6 30 114 11000 2.1 2.1 1.25 115 3100 1.31 2.751 1.25 116 2900 1.29 3.54879 1.25 117 2800 1.28 4.5424512 1.25 118 2600 1.26 5.72348851 1.25 119 2500 1.25 7.15436064 1.25 40 203 6500 1.65 1.65 1.65 50 120 8000 1.8 1.8 1.26 121 8200 1.82 3.276 1.26 122 7900 1.79 5.86404 1.26 123 6500 1.65 9.675666 1.26 124 5800 1.58 15.2875523 1.26 125 3200 1.32 20.179569 1.26 126 2700 1.27 25.6280526 1.26 127 2400 1.24 31.7787853 1.26 128 2200 1.22 38.770118 1.26 129 3300 1.33 51.564257 1.26 130 2800 1.28 66.0022489 1.26 131 2500 1.25 82.5028112 1.26 132 2100 1.21 99.8284015 1.26 133 3300 1.33 132.771774 1.26 134 2900 1.29 171.275589 1.26 135 2400 1.24 212.38173 1.26 136 2200 1.22 259.10571 1.26 137 3600 1.36 352.383766 1.26 138 3200 1.32 465.146571 1.26 139 2700 1.27 590.736145 1.26 140 2500 1.25 738.420182 1.26 141 3500 1.35 996.867245 1.26 142 3100 1.31 1305.89609 1.26 143 2600 1.26 1645.42907 1.26 144 2500 1.25 2056.78634 1.26 180 3200 1.32 2714.95797 1.26 181 3100 1.31 3556.59495 1.26 182 2500 1.25 4445.74368 1.26 183 2800 1.28 5690.55191 1.26 184 4200 1.42 8080.58372 1.26 185 4100 1.41 11393.623 1.26 186 3400 1.34 15267.4549 1.26 187 3000 1.3 19847.6913 1.26 188 3800 1.38 27389.814 1.26 189 3600 1.36 37250.1471 1.26 190 2900 1.29 48052.6898 1.26 191 2500 1.25 60065.8622 1.26 192 4000 1.4 84092.2071 1.26 193 3900 1.39 116888.168 1.26 194 3200 1.32 154292.382 1.26 195 2800 1.28 197494.248 1.26 196 3100 1.31 258717.465 1.26 197 3000 1.3 336332.705 1.26 198 2600 1.26 423779.208 1.26 199 2600 1.26 533961.802 1.26 60 103 9000 1.9 1.9 1.42 104 6000 1.6 3.04 1.42 105 4800 1.48 4.4992 1.42 106 4800 1.48 6.658816 1.42 107 4200 1.42 9.45551872 1.42 70 204 10000 2 2 2 80 145 14000 2.4 2.4 1.62 146 13500 2.35 5.64 1.62 147 12000 2.2 12.408 1.62 148 11000 2.1 26.0568 1.62 149 10500 2.05 53.41644 1.62 150 10000 2 106.83288 1.62 151 9500 1.95 208.324116 1.62 152 9000 1.9 395.81582 1.62 153 8000 1.8 712.468477 1.62 154 7500 1.75 1246.81983 1.62 155 7000 1.7 2119.59372 1.62 156 10000 2 4239.18744 1.62 157 9500 1.95 8266.4155 1.62 158 9000 1.9 15706.1895 1.62 159 8000 1.8 28271.141 1.62 160 7500 1.75 49474.4968 1.62 161 7000 1.7 84106.6445 1.62 162 10500 2.05 172418.621 1.62 163 9500 1.95 336216.311 1.62 164 7200 1.72 578292.056 1.62 165 6800 1.68 971530.654 1.62 166 6400 1.64 1593310.27 1.62 167 6200 1.62 2581162.64 1.62 168 11500 2.15 5549499.68 1.62 169 10000 2 11098999.4 1.62 170 9600 1.96 21754038.7 1.62 171 7400 1.74 37852027.4 1.62 172 7300 1.73 65484007.4 1.62 173 6100 1.61 105429252 1.62 174 11000 2.1 221401429 1.62 175 8800 1.88 416234687 1.62 176 8600 1.86 774196517 1.62 177 8400 1.84 1424521591 1.62 179 6200 1.62 2307724978 1.62 90 100 24000 3.4 3.4 2.7 101 17000 2.7 9.18 2.7 102 17000 2.7 24.786 2.7 100 108 12008 2.2008 2.2008 1.69 109 9000 1.9 4.18152 1.69 110 8200 1.82 7.6103664 1.69 111 7700 1.77 13.4703485 1.69 112 7800 1.78 23.9772204 1.69 113 6900 1.69 40.5215024 1.69 110 205 12008 2.2008 2.2008 1.83 206 8300 1.83 4.027464 1.83 178 7000 1.7 1.7 1.7 107 rows selected. RUNNING AND FINAL PRODUCTS: FINAL FIRST RULE, AUTOMATIC ORDER WITH multipliers AS ( SELECT department_id, employee_id, salary, (1 + salary/10000) mult, COUNT(*) OVER (PARTITION BY department_id) n_empsFROM employees
)
SELECT department_id, employee_id, salary, mult, running_prod, final_prodFROM multipliers
MODEL
PARTITION BY (department_id) DIMENSION BY (Row_Number() OVER (PARTITION BY department_id ORDER BY employee_id) rn) MEASURES (employee_id, salary, mult, mult running_prod, mult final_prod, n_emps) RULES AUTOMATIC ORDER ( final_prod = running_prod, running_prod = mult * running_prod)
ORDER BY department_id, employee_id/
DEPARTMENT_ID EMPLOYEE_ID SALARY MULT RUNNING_PROD FINAL_PROD ------------- ----------- ---------- ---------- ------------ ---------- 10 200 4400 1.44 1.44 1.44 20 201 13000 2.3 2.3 3.68 202 6000 1.6 3.68 3.68 30 114 11000 2.1 2.1 7.15436064 115 3100 1.31 2.751 7.15436064 116 2900 1.29 3.54879 7.15436064 117 2800 1.28 4.5424512 7.15436064 118 2600 1.26 5.72348851 7.15436064 119 2500 1.25 7.15436064 7.15436064 40 203 6500 1.65 1.65 1.65 50 120 8000 1.8 1.8 533961.802 121 8200 1.82 3.276 533961.802 122 7900 1.79 5.86404 533961.802 123 6500 1.65 9.675666 533961.802 124 5800 1.58 15.2875523 533961.802 125 3200 1.32 20.179569 533961.802 126 2700 1.27 25.6280526 533961.802 127 2400 1.24 31.7787853 533961.802 128 2200 1.22 38.770118 533961.802 129 3300 1.33 51.564257 533961.802 130 2800 1.28 66.0022489 533961.802 131 2500 1.25 82.5028112 533961.802 132 2100 1.21 99.8284015 533961.802 133 3300 1.33 132.771774 533961.802 134 2900 1.29 171.275589 533961.802 135 2400 1.24 212.38173 533961.802 136 2200 1.22 259.10571 533961.802 137 3600 1.36 352.383766 533961.802 138 3200 1.32 465.146571 533961.802 139 2700 1.27 590.736145 533961.802 140 2500 1.25 738.420182 533961.802 141 3500 1.35 996.867245 533961.802 142 3100 1.31 1305.89609 533961.802 143 2600 1.26 1645.42907 533961.802 144 2500 1.25 2056.78634 533961.802 180 3200 1.32 2714.95797 533961.802 181 3100 1.31 3556.59495 533961.802 182 2500 1.25 4445.74368 533961.802 183 2800 1.28 5690.55191 533961.802 184 4200 1.42 8080.58372 533961.802 185 4100 1.41 11393.623 533961.802 186 3400 1.34 15267.4549 533961.802 187 3000 1.3 19847.6913 533961.802 188 3800 1.38 27389.814 533961.802 189 3600 1.36 37250.1471 533961.802 190 2900 1.29 48052.6898 533961.802 191 2500 1.25 60065.8622 533961.802 192 4000 1.4 84092.2071 533961.802 193 3900 1.39 116888.168 533961.802 194 3200 1.32 154292.382 533961.802 195 2800 1.28 197494.248 533961.802 196 3100 1.31 258717.465 533961.802 197 3000 1.3 336332.705 533961.802 198 2600 1.26 423779.208 533961.802 199 2600 1.26 533961.802 533961.802 60 103 9000 1.9 1.9 9.45551872 104 6000 1.6 3.04 9.45551872 105 4800 1.48 4.4992 9.45551872 106 4800 1.48 6.658816 9.45551872 107 4200 1.42 9.45551872 9.45551872 70 204 10000 2 2 2 80 145 14000 2.4 2.4 2307724978 146 13500 2.35 5.64 2307724978 147 12000 2.2 12.408 2307724978 148 11000 2.1 26.0568 2307724978 149 10500 2.05 53.41644 2307724978 150 10000 2 106.83288 2307724978 151 9500 1.95 208.324116 2307724978 152 9000 1.9 395.81582 2307724978 153 8000 1.8 712.468477 2307724978 154 7500 1.75 1246.81983 2307724978 155 7000 1.7 2119.59372 2307724978 156 10000 2 4239.18744 2307724978 157 9500 1.95 8266.4155 2307724978 158 9000 1.9 15706.1895 2307724978 159 8000 1.8 28271.141 2307724978 160 7500 1.75 49474.4968 2307724978 161 7000 1.7 84106.6445 2307724978 162 10500 2.05 172418.621 2307724978 163 9500 1.95 336216.311 2307724978 164 7200 1.72 578292.056 2307724978 165 6800 1.68 971530.654 2307724978 166 6400 1.64 1593310.27 2307724978 167 6200 1.62 2581162.64 2307724978 168 11500 2.15 5549499.68 2307724978 169 10000 2 11098999.4 2307724978 170 9600 1.96 21754038.7 2307724978 171 7400 1.74 37852027.4 2307724978 172 7300 1.73 65484007.4 2307724978 173 6100 1.61 105429252 2307724978 174 11000 2.1 221401429 2307724978 175 8800 1.88 416234687 2307724978 176 8600 1.86 774196517 2307724978 177 8400 1.84 1424521591 2307724978 179 6200 1.62 2307724978 2307724978 90 100 24000 3.4 3.4 24.786 101 17000 2.7 9.18 24.786 102 17000 2.7 24.786 24.786 100 108 12008 2.2008 2.2008 40.5215024 109 9000 1.9 4.18152 40.5215024 110 8200 1.82 7.6103664 40.5215024 111 7700 1.77 13.4703485 40.5215024 112 7800 1.78 23.9772204 40.5215024 113 6900 1.69 40.5215024 40.5215024 110 205 12008 2.2008 2.2008 4.027464 206 8300 1.83 4.027464 4.027464 178 7000 1.7 1.7 1.7 107 rows selected. AVERAGE AND MOVING AVERAGE SELECT department_id, employee_id, salary, avg_salary, moving_avg_salary_3FROM employees
MODEL
PARTITION BY (department_id) DIMENSION BY (Row_Number() OVER (PARTITION BY department_id ORDER BY employee_id) rn) MEASURES (employee_id, salary, salary avg_salary, salary moving_avg_salary_3)RULES (
avg_salary = AVG(salary), moving_avg_salary_3 = AVG(salary))
ORDER BY department_id, employee_id/
DEPARTMENT_ID EMPLOYEE_ID SALARY AVG_SALARY MOVING_AVG_SALARY_3 ------------- ----------- ---------- ---------- ------------------- 10 200 4400 4400 4400 20 201 13000 9500 13000 202 6000 9500 9500 30 114 11000 4150 11000 115 3100 4150 7050 116 2900 4150 5666.66667 117 2800 4150 2933.33333 118 2600 4150 2766.66667 119 2500 4150 2633.33333 40 203 6500 6500 6500 50 120 8000 3475.55556 8000 121 8200 3475.55556 8100 122 7900 3475.55556 8033.33333 123 6500 3475.55556 7533.33333 124 5800 3475.55556 6733.33333 125 3200 3475.55556 5166.66667 126 2700 3475.55556 3900 127 2400 3475.55556 2766.66667 128 2200 3475.55556 2433.33333 129 3300 3475.55556 2633.33333 130 2800 3475.55556 2766.66667 131 2500 3475.55556 2866.66667 132 2100 3475.55556 2466.66667 133 3300 3475.55556 2633.33333 134 2900 3475.55556 2766.66667 135 2400 3475.55556 2866.66667 136 2200 3475.55556 2500 137 3600 3475.55556 2733.33333 138 3200 3475.55556 3000 139 2700 3475.55556 3166.66667 140 2500 3475.55556 2800 141 3500 3475.55556 2900 142 3100 3475.55556 3033.33333 143 2600 3475.55556 3066.66667 144 2500 3475.55556 2733.33333 180 3200 3475.55556 2766.66667 181 3100 3475.55556 2933.33333 182 2500 3475.55556 2933.33333 183 2800 3475.55556 2800 184 4200 3475.55556 3166.66667 185 4100 3475.55556 3700 186 3400 3475.55556 3900 187 3000 3475.55556 3500 188 3800 3475.55556 3400 189 3600 3475.55556 3466.66667 190 2900 3475.55556 3433.33333 191 2500 3475.55556 3000 192 4000 3475.55556 3133.33333 193 3900 3475.55556 3466.66667 194 3200 3475.55556 3700 195 2800 3475.55556 3300 196 3100 3475.55556 3033.33333 197 3000 3475.55556 2966.66667 198 2600 3475.55556 2900 199 2600 3475.55556 2733.33333 60 103 9000 5760 9000 104 6000 5760 7500 105 4800 5760 6600 106 4800 5760 5200 107 4200 5760 4600 70 204 10000 10000 10000 80 145 14000 8955.88235 14000 146 13500 8955.88235 13750 147 12000 8955.88235 13166.6667 148 11000 8955.88235 12166.6667 149 10500 8955.88235 11166.6667 150 10000 8955.88235 10500 151 9500 8955.88235 10000 152 9000 8955.88235 9500 153 8000 8955.88235 8833.33333 154 7500 8955.88235 8166.66667 155 7000 8955.88235 7500 156 10000 8955.88235 8166.66667 157 9500 8955.88235 8833.33333 158 9000 8955.88235 9500 159 8000 8955.88235 8833.33333 160 7500 8955.88235 8166.66667 161 7000 8955.88235 7500 162 10500 8955.88235 8333.33333 163 9500 8955.88235 9000 164 7200 8955.88235 9066.66667 165 6800 8955.88235 7833.33333 166 6400 8955.88235 6800 167 6200 8955.88235 6466.66667 168 11500 8955.88235 8033.33333 169 10000 8955.88235 9233.33333 170 9600 8955.88235 10366.6667 171 7400 8955.88235 9000 172 7300 8955.88235 8100 173 6100 8955.88235 6933.33333 174 11000 8955.88235 8133.33333 175 8800 8955.88235 8633.33333 176 8600 8955.88235 9466.66667 177 8400 8955.88235 8600 179 6200 8955.88235 7733.33333 90 100 24000 19333.3333 24000 101 17000 19333.3333 20500 102 17000 19333.3333 19333.3333 100 108 12008 8601.33333 12008 109 9000 8601.33333 10504 110 8200 8601.33333 9736 111 7700 8601.33333 8300 112 7800 8601.33333 7900 113 6900 8601.33333 7466.66667 110 205 12008 10154 12008 206 8300 10154 10154 178 7000 7000 7000 107 rows selected. UPSERT WITH FOR LOOP: SPLIT RECORDS INTO TWO WITH SALARY HALVED SELECT department_id, employee_id, old_salary, split_salaryFROM employees
MODEL
PARTITION BY (department_id) DIMENSION BY (Row_Number() OVER (PARTITION BY department_id ORDER BY employee_id) rn) MEASURES (employee_id, salary old_salary, salary split_salary, Count(*) OVER (PARTITION BY department_id) as n_emps)RULES UPSERT (
employee_id =
employee_id,
split_salary =
old_salary,
split_salary = 0.5 * split_salary)
ORDER BY department_id, employee_id/
DEPARTMENT_ID EMPLOYEE_ID OLD_SALARY SPLIT_SALARY ------------- ----------- ---------- ------------ 10 200 4400 2200 200 2200 20 201 13000 6500 201 6500 202 3000 202 6000 3000 30 114 5500 114 11000 5500 115 3100 1550 115 1550 116 1450 116 2900 1450 117 1400 117 2800 1400 118 1300 118 2600 1300 119 2500 1250 119 1250 40 203 6500 3250 203 3250 50 120 8000 4000 120 4000 121 4100 121 8200 4100 122 3950 122 7900 3950 123 6500 3250 123 3250 124 2900 124 5800 2900 125 1600 125 3200 1600 126 2700 1350 126 1350 127 2400 1200 127 1200 128 1100 128 2200 1100 129 1650 129 3300 1650 130 1400 130 2800 1400 131 1250 131 2500 1250 132 1050 132 2100 1050 133 3300 1650 133 1650 134 2900 1450 134 1450 135 1200 135 2400 1200 136 2200 1100 136 1100 137 3600 1800 137 1800 138 1600 138 3200 1600 139 1350 139 2700 1350 140 1250 140 2500 1250 141 1750 141 3500 1750 142 1550 142 3100 1550 143 1300 143 2600 1300 144 1250 144 2500 1250 180 3200 1600 180 1600 181 3100 1550 181 1550 182 2500 1250 182 1250 183 2800 1400 183 1400 184 4200 2100 184 2100 185 4100 2050 185 2050 186 1700 186 3400 1700 187 1500 187 3000 1500 188 3800 1900 188 1900 189 3600 1800 189 1800 190 1450 190 2900 1450 191 1250 191 2500 1250 192 4000 2000 192 2000 193 1950 193 3900 1950 194 1600 194 3200 1600 195 2800 1400 195 1400 196 1550 196 3100 1550 197 3000 1500 197 1500 198 2600 1300 198 1300 199 2600 1300 199 1300 60 103 9000 4500 103 4500 104 6000 3000 104 3000 105 4800 2400 105 2400 106 2400 106 4800 2400 107 2100 107 4200 2100 70 204 5000 204 10000 5000 80 145 7000 145 14000 7000 146 13500 6750 146 6750 147 12000 6000 147 6000 148 11000 5500 148 5500 149 10500 5250 149 5250 150 10000 5000 150 5000 151 9500 4750 151 4750 152 9000 4500 152 4500 153 4000 153 8000 4000 154 3750 154 7500 3750 155 3500 155 7000 3500 156 5000 156 10000 5000 157 4750 157 9500 4750 158 4500 158 9000 4500 159 8000 4000 159 4000 160 3750 160 7500 3750 161 3500 161 7000 3500 162 10500 5250 162 5250 163 4750 163 9500 4750 164 3600 164 7200 3600 165 3400 165 6800 3400 166 6400 3200 166 3200 167 6200 3100 167 3100 168 11500 5750 168 5750 169 10000 5000 169 5000 170 9600 4800 170 4800 171 7400 3700 171 3700 172 3650 172 7300 3650 173 6100 3050 173 3050 174 11000 5500 174 5500 175 8800 4400 175 4400 176 8600 4300 176 4300 177 8400 4200 177 4200 179 6200 3100 179 3100 90 100 12000 100 24000 12000 101 17000 8500 101 8500 102 17000 8500 102 8500 100 108 12008 6004 108 6004 109 9000 4500 109 4500 110 8200 4100 110 4100 111 3850 111 7700 3850 112 7800 3900 112 3900 113 6900 3450 113 3450 110 205 12008 6004 205 6004 206 8300 4150 206 4150 178 3500 178 7000 3500 214 rows selected. ITERATE: TAKE SQUARE ROOT OF SALARY ITERATIVELY UNTIL AVERAGE < 10 SELECT department_id, employee_id, salary, avg_salaryFROM employees
MODEL
PARTITION BY (department_id) DIMENSION BY (Row_Number() OVER (PARTITION BY department_id ORDER BY employee_id) rn) MEASURES (employee_id, salary, salary avg_salary, salary moving_avg_salary_3) RULES ITERATE (100) UNTIL avg_salary < 10.0 ( salary = SQRT(salary), avg_salary = AVG(salary))
ORDER BY department_id, employee_id/
DEPARTMENT_ID EMPLOYEE_ID SALARY AVG_SALARY ------------- ----------- ---------- ---------- 10 200 8.1444764 8.1444764 20 201 10.6778997 9.73950855 202 8.80111737 9.73950855 30 114 10.2411369 7.75455528 115 7.46174535 7.75455528 116 7.33836822 7.75455528 117 7.27427153 7.75455528 118 7.14074192 7.75455528 119 7.07106781 7.75455528 40 203 8.9790076 8.9790076 50 120 9.45741609 7.58389021 121 9.51597874 7.58389021 122 9.42772211 7.58389021 123 8.9790076 7.58389021 124 8.72683969 7.58389021 125 7.52120619 7.58389021 126 7.20843424 7.58389021 127 6.99927102 7.58389021 128 6.84866101 7.58389021 129 7.57928931 7.58389021 130 7.27427153 7.58389021 131 7.07106781 7.58389021 132 6.76947243 7.58389021 133 7.57928931 7.58389021 134 7.33836822 7.58389021 135 6.99927102 7.58389021 136 6.84866101 7.58389021 137 7.74596669 7.58389021 138 7.52120619 7.58389021 139 7.20843424 7.58389021 140 7.07106781 7.58389021 141 7.69160567 7.58389021 142 7.46174535 7.58389021 143 7.14074192 7.58389021 144 7.07106781 7.58389021 180 7.52120619 7.58389021 181 7.46174535 7.58389021 182 7.07106781 7.58389021 183 7.27427153 7.58389021 184 8.05030478 7.58389021 185 8.00195241 7.58389021 186 7.63606698 7.58389021 187 7.40082804 7.58389021 188 7.85137822 7.58389021 189 7.74596669 7.58389021 190 7.33836822 7.58389021 191 7.07106781 7.58389021 192 7.95270729 7.58389021 193 7.90252997 7.58389021 194 7.52120619 7.58389021 195 7.27427153 7.58389021 196 7.46174535 7.58389021 197 7.40082804 7.58389021 198 7.14074192 7.58389021 199 7.14074192 7.58389021 60 103 9.74003746 8.64772508 104 8.80111737 8.64772508 105 8.3235829 8.64772508 106 8.3235829 8.64772508 107 8.05030478 8.64772508 70 204 3.16227766 3.16227766 80 145 10.8775731 9.6838805 146 10.7791234 9.6838805 147 10.4663514 9.6838805 148 10.2411369 9.6838805 149 10.1227223 9.6838805 150 10 9.6838805 151 9.87258545 9.6838805 152 9.74003746 9.6838805 153 9.45741609 9.6838805 154 9.30604859 9.6838805 155 9.14691219 9.6838805 156 10 9.6838805 157 9.87258545 9.6838805 158 9.74003746 9.6838805 159 9.45741609 9.6838805 160 9.30604859 9.6838805 161 9.14691219 9.6838805 162 10.1227223 9.6838805 163 9.87258545 9.6838805 164 9.2115587 9.6838805 165 9.08086519 9.6838805 166 8.94427191 9.6838805 167 8.87356066 9.6838805 168 10.3555808 9.6838805 169 10 9.6838805 170 9.89846401 9.6838805 171 9.27487211 9.6838805 172 9.24337803 9.6838805 173 8.8375617 9.6838805 174 10.2411369 9.6838805 175 9.68546928 9.6838805 176 9.62996287 9.6838805 177 9.57347972 9.6838805 179 8.87356066 9.6838805 90 100 3.52798236 3.42875376 101 3.37913945 3.42875376 102 3.37913945 3.42875376 100 108 10.4680953 9.6005671 109 9.74003746 9.6005671 110 9.51597874 9.6005671 111 9.36747799 9.6005671 112 9.39774487 9.6005671 113 9.11406817 9.6005671 110 205 3.23544361 3.16245958 206 3.08947554 3.16245958 178 9.14691219 9.14691219 107 rows selected. WITHIN-RULE ORDER DEFAULT ASCENDING: SET SALARY = PREVIOUS SALARY PROMPT Within-Rule Order Default Ascending: Set salary = previous salary SELECT department_id, employee_id, old_salary, salaryFROM employees
MODEL
PARTITION BY (department_id) DIMENSION BY (Row_Number() OVER (PARTITION BY department_id ORDER BY employee_id) rn) MEASURES (employee_id, salary old_salary, salary)RULES (
salary = salary
)
ORDER BY department_id, employee_id/
DEPARTMENT_ID EMPLOYEE_ID OLD_SALARY SALARY ------------- ----------- ---------- ---------- 10 200 4400 4400 20 201 13000 13000 202 6000 13000 30 114 11000 11000 115 3100 11000 116 2900 11000 117 2800 11000 118 2600 11000 119 2500 11000 40 203 6500 6500 50 120 8000 8000 121 8200 8000 122 7900 8000 123 6500 8000 124 5800 8000 125 3200 8000 126 2700 8000 127 2400 8000 128 2200 8000 129 3300 8000 130 2800 8000 131 2500 8000 132 2100 8000 133 3300 8000 134 2900 8000 135 2400 8000 136 2200 8000 137 3600 8000 138 3200 8000 139 2700 8000 140 2500 8000 141 3500 8000 142 3100 8000 143 2600 8000 144 2500 8000 180 3200 8000 181 3100 8000 182 2500 8000 183 2800 8000 184 4200 8000 185 4100 8000 186 3400 8000 187 3000 8000 188 3800 8000 189 3600 8000 190 2900 8000 191 2500 8000 192 4000 8000 193 3900 8000 194 3200 8000 195 2800 8000 196 3100 8000 197 3000 8000 198 2600 8000 199 2600 8000 60 103 9000 9000 104 6000 9000 105 4800 9000 106 4800 9000 107 4200 9000 70 204 10000 10000 80 145 14000 14000 146 13500 14000 147 12000 14000 148 11000 14000 149 10500 14000 150 10000 14000 151 9500 14000 152 9000 14000 153 8000 14000 154 7500 14000 155 7000 14000 156 10000 14000 157 9500 14000 158 9000 14000 159 8000 14000 160 7500 14000 161 7000 14000 162 10500 14000 163 9500 14000 164 7200 14000 165 6800 14000 166 6400 14000 167 6200 14000 168 11500 14000 169 10000 14000 170 9600 14000 171 7400 14000 172 7300 14000 173 6100 14000 174 11000 14000 175 8800 14000 176 8600 14000 177 8400 14000 179 6200 14000 90 100 24000 24000 101 17000 24000 102 17000 24000 100 108 12008 12008 109 9000 12008 110 8200 12008 111 7700 12008 112 7800 12008 113 6900 12008 110 205 12008 12008 206 8300 12008 178 7000 7000 107 rows selected. WITHIN-RULE ORDER DESCENDING: SET SALARY = PREVIOUS SALARY SELECT department_id, employee_id, old_salary, salaryFROM employees
MODEL
PARTITION BY (department_id) DIMENSION BY (Row_Number() OVER (PARTITION BY department_id ORDER BY employee_id) rn) MEASURES (employee_id, salary old_salary, salary)RULES (
salary ORDER BY rn DESC = salary)
ORDER BY department_id, employee_id/
DEPARTMENT_ID EMPLOYEE_ID OLD_SALARY SALARY ------------- ----------- ---------- ---------- 10 200 4400 4400 20 201 13000 13000 202 6000 13000 30 114 11000 11000 115 3100 11000 116 2900 3100 117 2800 2900 118 2600 2800 119 2500 2600 40 203 6500 6500 50 120 8000 8000 121 8200 8000 122 7900 8200 123 6500 7900 124 5800 6500 125 3200 5800 126 2700 3200 127 2400 2700 128 2200 2400 129 3300 2200 130 2800 3300 131 2500 2800 132 2100 2500 133 3300 2100 134 2900 3300 135 2400 2900 136 2200 2400 137 3600 2200 138 3200 3600 139 2700 3200 140 2500 2700 141 3500 2500 142 3100 3500 143 2600 3100 144 2500 2600 180 3200 2500 181 3100 3200 182 2500 3100 183 2800 2500 184 4200 2800 185 4100 4200 186 3400 4100 187 3000 3400 188 3800 3000 189 3600 3800 190 2900 3600 191 2500 2900 192 4000 2500 193 3900 4000 194 3200 3900 195 2800 3200 196 3100 2800 197 3000 3100 198 2600 3000 199 2600 2600 60 103 9000 9000 104 6000 9000 105 4800 6000 106 4800 4800 107 4200 4800 70 204 10000 10000 80 145 14000 14000 146 13500 14000 147 12000 13500 148 11000 12000 149 10500 11000 150 10000 10500 151 9500 10000 152 9000 9500 153 8000 9000 154 7500 8000 155 7000 7500 156 10000 7000 157 9500 10000 158 9000 9500 159 8000 9000 160 7500 8000 161 7000 7500 162 10500 7000 163 9500 10500 164 7200 9500 165 6800 7200 166 6400 6800 167 6200 6400 168 11500 6200 169 10000 11500 170 9600 10000 171 7400 9600 172 7300 7400 173 6100 7300 174 11000 6100 175 8800 11000 176 8600 8800 177 8400 8600 179 6200 8400 90 100 24000 24000 101 17000 24000 102 17000 17000 100 108 12008 12008 109 9000 12008 110 8200 9000 111 7700 8200 112 7800 7700 113 6900 7800 110 205 12008 12008 206 8300 12008 178 7000 7000 107 rows selected. RECURSIVE SUBQUERY FACTORS Oracle Doc: Recursive Subquery Factoring SQL Recursive Subquery Factors in a Tweet EMPLOYEE TREE: CONNECT BYWITH cby AS (
SELECT last_name, employee_id, manager_id, LEVEL lvlFROM employees
START WITH employee_id = 100 CONNECT BY PRIOR employee_id = manager_id ORDER SIBLINGS BY last_name)
SELECT employee_id, LPad('.', 3*(lvl - 1), '.') || last_name last_name,manager_id,
lvl
FROM cby
/
EMPLOYEE_ID LAST_NAME MANAGER_ID LVL ----------- -------------------- ---------- ---------- 100 King 1 148 ...Cambrault 100 2 172 ......Bates 148 3 169 ......Bloom 148 3 170 ......Fox 148 3 173 ......Kumar 148 3 168 ......Ozer 148 3 171 ......Smith 148 3 102 ...De Haan 100 2 103 ......Hunold 102 3 105 .........Austin 103 4 104 .........Ernst 103 4 107 .........Lorentz 103 4 106 .........Pataballa 103 4 147 ...Errazuriz 100 2 166 ......Ande 147 3 167 ......Banda 147 3 163 ......Greene 147 3 165 ......Lee 147 3 164 ......Marvins 147 3 162 ......Vishney 147 3 121 ...Fripp 100 2 130 ......Atkinson 121 3 129 ......Bissot 121 3 185 ......Bull 121 3 187 ......Cabrio 121 3 186 ......Dellinger 121 3 131 ......Marlow 121 3 132 ......Olson 121 3 184 ......Sarchand 121 3 201 ...Hartstein 100 2 202 ......Fay 201 3 122 ...Kaufling 100 2 188 ......Chung 122 3 189 ......Dilly 122 3 190 ......Gates 122 3 135 ......Gee 122 3 133 ......Mallin 122 3 191 ......Perkins 122 3 136 ......Philtanker 122 3 134 ......Rogers 122 3 101 ...Kochhar 100 2 204 ......Baer 101 3 108 ......Greenberg 101 3 110 .........Chen 108 4 109 .........Faviet 108 4 113 .........Popp 108 4 111 .........Sciarra 108 4 112 .........Urman 108 4 205 ......Higgins 101 3 206 .........Gietz 205 4 203 ......Mavris 101 3 200 ......Whalen 101 3 124 ...Mourgos 100 2 142 ......Davies 124 3 197 ......Feeney 124 3 199 ......Grant 124 3 143 ......Matos 124 3 198 ......OConnell 124 3 141 ......Rajs 124 3 144 ......Vargas 124 3 196 ......Walsh 124 3 146 ...Partners 100 2 160 ......Doran 146 3 156 ......King 146 3 158 ......McEwen 146 3 161 ......Sewall 146 3 159 ......Smith 146 3 157 ......Sully 146 3 114 ...Raphaely 100 2 116 ......Baida 114 3 119 ......Colmenares 114 3 118 ......Himuro 114 3 115 ......Khoo 114 3 117 ......Tobias 114 3 145 ...Russell 100 2 151 ......Bernstein 145 3 154 ......Cambrault 145 3 152 ......Hall 145 3 153 ......Olsen 145 3 150 ......Tucker 145 3 155 ......Tuvault 145 3 123 ...Vollman 100 2 192 ......Bell 123 3 193 ......Everett 123 3 195 ......Jones 123 3 137 ......Ladwig 123 3 194 ......McCain 123 3 140 ......Patel 123 3 139 ......Seo 123 3 138 ......Stiles 123 3 120 ...Weiss 100 2 181 ......Fleaur 120 3 183 ......Geoni 120 3 127 ......Landry 120 3 128 ......Markle 120 3 126 ......Mikkilineni 120 3 125 ......Nayer 120 3 182 ......Sullivan 120 3 180 ......Taylor 120 3 149 ...Zlotkey 100 2 174 ......Abel 149 3 178 ......Grant 149 3 175 ......Hutton 149 3 179 ......Johnson 149 3 177 ......Livingston 149 3 176 ......Taylor 149 3 107 rows selected. EMPLOYEE TREE: RECURSIVE SUBQUERY FACTORS, DEPTH FIRST The result for the following query is the same as for the aboveCONNECT BY query:
WITH rsf(employee_id, last_name, manager_id, lvl) AS ( SELECT employee_id,last_name,
manager_id,
1 lvl
FROM employees
WHERE manager_id IS NULLUNION ALL
SELECT e.employee_id,e.last_name,
e.manager_id,
r.lvl + 1
FROM rsf r
JOIN employees e ON e.manager_id = r.employee_id ) SEARCH DEPTH FIRST BY last_name SET ord_by SELECT employee_id, LPad('.', 3*(lvl - 1), '.') || last_name last_name,manager_id,
lvl
FROM rsf
ORDER BY ord_by
/
EMPLOYEE TREE: RECURSIVE SUBQUERY FACTORS, BREADTH FIRST WITH rsf(employee_id, last_name, manager_id, lvl) AS ( SELECT employee_id,last_name,
manager_id,
1 lvl
FROM employees
WHERE manager_id IS NULLUNION ALL
SELECT e.employee_id,e.last_name,
e.manager_id,
r.lvl + 1
FROM rsf r
JOIN employees e ON e.manager_id = r.employee_id ) SEARCH BREADTH FIRST BY last_name SET ord_by SELECT employee_id, LPad('.', 3*(lvl - 1), '.') || last_name last_name,manager_id,
lvl
FROM rsf
ORDER BY ord_by
/
EMPLOYEE_ID LAST_NAME MANAGER_ID LVL ----------- -------------------- ---------- ---------- 100 King 1 148 ...Cambrault 100 2 102 ...De Haan 100 2 147 ...Errazuriz 100 2 121 ...Fripp 100 2 201 ...Hartstein 100 2 122 ...Kaufling 100 2 101 ...Kochhar 100 2 124 ...Mourgos 100 2 146 ...Partners 100 2 114 ...Raphaely 100 2 145 ...Russell 100 2 123 ...Vollman 100 2 120 ...Weiss 100 2 149 ...Zlotkey 100 2 174 ......Abel 149 3 166 ......Ande 147 3 130 ......Atkinson 121 3 204 ......Baer 101 3 116 ......Baida 114 3 167 ......Banda 147 3 172 ......Bates 148 3 192 ......Bell 123 3 151 ......Bernstein 145 3 129 ......Bissot 121 3 169 ......Bloom 148 3 185 ......Bull 121 3 187 ......Cabrio 121 3 154 ......Cambrault 145 3 188 ......Chung 122 3 119 ......Colmenares 114 3 142 ......Davies 124 3 186 ......Dellinger 121 3 189 ......Dilly 122 3 160 ......Doran 146 3 193 ......Everett 123 3 202 ......Fay 201 3 197 ......Feeney 124 3 181 ......Fleaur 120 3 170 ......Fox 148 3 190 ......Gates 122 3 135 ......Gee 122 3 183 ......Geoni 120 3 199 ......Grant 124 3 178 ......Grant 149 3 108 ......Greenberg 101 3 163 ......Greene 147 3 152 ......Hall 145 3 205 ......Higgins 101 3 118 ......Himuro 114 3 103 ......Hunold 102 3 175 ......Hutton 149 3 179 ......Johnson 149 3 195 ......Jones 123 3 115 ......Khoo 114 3 156 ......King 146 3 173 ......Kumar 148 3 137 ......Ladwig 123 3 127 ......Landry 120 3 165 ......Lee 147 3 177 ......Livingston 149 3 133 ......Mallin 122 3 128 ......Markle 120 3 131 ......Marlow 121 3 164 ......Marvins 147 3 143 ......Matos 124 3 203 ......Mavris 101 3 194 ......McCain 123 3 158 ......McEwen 146 3 126 ......Mikkilineni 120 3 125 ......Nayer 120 3 198 ......OConnell 124 3 153 ......Olsen 145 3 132 ......Olson 121 3 168 ......Ozer 148 3 140 ......Patel 123 3 191 ......Perkins 122 3 136 ......Philtanker 122 3 141 ......Rajs 124 3 134 ......Rogers 122 3 184 ......Sarchand 121 3 139 ......Seo 123 3 161 ......Sewall 146 3 171 ......Smith 148 3 159 ......Smith 146 3 138 ......Stiles 123 3 182 ......Sullivan 120 3 157 ......Sully 146 3 180 ......Taylor 120 3 176 ......Taylor 149 3 117 ......Tobias 114 3 150 ......Tucker 145 3 155 ......Tuvault 145 3 144 ......Vargas 124 3 162 ......Vishney 147 3 196 ......Walsh 124 3 200 ......Whalen 101 3 105 .........Austin 103 4 110 .........Chen 108 4 104 .........Ernst 103 4 109 .........Faviet 108 4 206 .........Gietz 205 4 107 .........Lorentz 103 4 106 .........Pataballa 103 4 113 .........Popp 108 4 111 .........Sciarra 108 4 112 .........Urman 108 4 107 rows selected. PRODUCTS USING RECURSIVE SUBQUERY FACTORS: PASSING THROUGH EXPRESSIONS WITH multipliers AS ( SELECT department_id, employee_id, salary, (1 + salary/10000) mult, Row_Number() OVER (PARTITION BY department_id ORDER BY employee_id) rnFROM employees
), rsf (department_id, employee_id, rn, salary, mult, running_prod, lvl) AS ( SELECT department_id, employee_id, rn, salary, mult, mult running_prod, 1 lvlFROM multipliers
WHERE rn = 1
UNION ALL
SELECT m.department_id, m.employee_id, m.rn, m.salary, m.mult, r.running_prod * m.mult, r.lvl + 1FROM rsf r
JOIN multipliers m ON m.rn = r.rn + 1 AND m.department_id = r.department_id)
SELECT department_id, employee_id, salary, mult, running_prod, lvlFROM rsf
ORDER BY department_id, employee_id/
DEPARTMENT_ID EMPLOYEE_ID SALARY MULT RUNNING_PROD LVL ------------- ----------- ---------- ---------- ------------ ---------- 10 200 4400 1.44 1.44 1 20 201 13000 2.3 2.3 1 202 6000 1.6 3.68 2 30 114 11000 2.1 2.1 1 115 3100 1.31 2.751 2 116 2900 1.29 3.54879 3 117 2800 1.28 4.5424512 4 118 2600 1.26 5.72348851 5 119 2500 1.25 7.15436064 6 40 203 6500 1.65 1.65 1 50 120 8000 1.8 1.8 1 121 8200 1.82 3.276 2 122 7900 1.79 5.86404 3 123 6500 1.65 9.675666 4 124 5800 1.58 15.2875523 5 125 3200 1.32 20.179569 6 126 2700 1.27 25.6280526 7 127 2400 1.24 31.7787853 8 128 2200 1.22 38.770118 9 129 3300 1.33 51.564257 10 130 2800 1.28 66.0022489 11 131 2500 1.25 82.5028112 12 132 2100 1.21 99.8284015 13 133 3300 1.33 132.771774 14 134 2900 1.29 171.275589 15 135 2400 1.24 212.38173 16 136 2200 1.22 259.10571 17 137 3600 1.36 352.383766 18 138 3200 1.32 465.146571 19 139 2700 1.27 590.736145 20 140 2500 1.25 738.420182 21 141 3500 1.35 996.867245 22 142 3100 1.31 1305.89609 23 143 2600 1.26 1645.42907 24 144 2500 1.25 2056.78634 25 180 3200 1.32 2714.95797 26 181 3100 1.31 3556.59495 27 182 2500 1.25 4445.74368 28 183 2800 1.28 5690.55191 29 184 4200 1.42 8080.58372 30 185 4100 1.41 11393.623 31 186 3400 1.34 15267.4549 32 187 3000 1.3 19847.6913 33 188 3800 1.38 27389.814 34 189 3600 1.36 37250.1471 35 190 2900 1.29 48052.6898 36 191 2500 1.25 60065.8622 37 192 4000 1.4 84092.2071 38 193 3900 1.39 116888.168 39 194 3200 1.32 154292.382 40 195 2800 1.28 197494.248 41 196 3100 1.31 258717.465 42 197 3000 1.3 336332.705 43 198 2600 1.26 423779.208 44 199 2600 1.26 533961.802 45 60 103 9000 1.9 1.9 1 104 6000 1.6 3.04 2 105 4800 1.48 4.4992 3 106 4800 1.48 6.658816 4 107 4200 1.42 9.45551872 5 70 204 10000 2 2 1 80 145 14000 2.4 2.4 1 146 13500 2.35 5.64 2 147 12000 2.2 12.408 3 148 11000 2.1 26.0568 4 149 10500 2.05 53.41644 5 150 10000 2 106.83288 6 151 9500 1.95 208.324116 7 152 9000 1.9 395.81582 8 153 8000 1.8 712.468477 9 154 7500 1.75 1246.81983 10 155 7000 1.7 2119.59372 11 156 10000 2 4239.18744 12 157 9500 1.95 8266.4155 13 158 9000 1.9 15706.1895 14 159 8000 1.8 28271.141 15 160 7500 1.75 49474.4968 16 161 7000 1.7 84106.6445 17 162 10500 2.05 172418.621 18 163 9500 1.95 336216.311 19 164 7200 1.72 578292.056 20 165 6800 1.68 971530.654 21 166 6400 1.64 1593310.27 22 167 6200 1.62 2581162.64 23 168 11500 2.15 5549499.68 24 169 10000 2 11098999.4 25 170 9600 1.96 21754038.7 26 171 7400 1.74 37852027.4 27 172 7300 1.73 65484007.4 28 173 6100 1.61 105429252 29 174 11000 2.1 221401429 30 175 8800 1.88 416234687 31 176 8600 1.86 774196517 32 177 8400 1.84 1424521591 33 179 6200 1.62 2307724978 34 90 100 24000 3.4 3.4 1 101 17000 2.7 9.18 2 102 17000 2.7 24.786 3 100 108 12008 2.2008 2.2008 1 109 9000 1.9 4.18152 2 110 8200 1.82 7.6103664 3 111 7700 1.77 13.4703485 4 112 7800 1.78 23.9772204 5 113 6900 1.69 40.5215024 6 110 205 12008 2.2008 2.2008 1 206 8300 1.83 4.027464 2 178 7000 1.7 1.7 1 107 rows selected. Here's a query structure diagram for the query: and here's a diagram showing partitioning and flow through theiterations:
You can see the scripts and full output on my new GitHub project, Small SQL projects , in the analytics_and_recursion_explainers folder. Here's an article from 2017 where you can see recursive SQL techniques used to solve a variety of difficult combinatorial optimization problems: Knapsacks and Networks in SQL.
Share24
Posted in Analytics , Oracle, QSD
, Recursive
, SQL
, Subquery Factor
| Tagged Analytics
, hierarchical
, model
, QSD
, Recursive
, SQL
, Subquery Factor
| Leave a Reply
A NOTE ON RUNNING SUMS AND PRODUCTS IN SQL Posted on January 19, 2020 byBrendanP
Reply
It’s a common requirement to compute running sums over time in SQL; for example, to find sales volumes to date. This is easy to do using analytic functions, like this, schematically: SELECT SUM(sales) OVER (PARTITION By partition_key ORDER BY date)FROM ...
The ORDER BY clause implicitly adds a window range of UNBOUNDED PRECEDING, and if you omit it you get the overall totals bypartition_key.
Recently I needed to compute the products of some numeric factors across records over time. This kind of requirement often arises in financial calculations, and is slightly more tricky since Oracle doesn’t have an analytic function for products as it does for sums. However, we can achieve the same functionality using the well known mathematical equivalence: log(xy) = log(x) + log(y)and therefore:
xy = exp(log(x) + log(y)) More generally, if we have a set of N records , and a function, f, defined on a record, we can write the running sum, for function f, andn in (1..N) as:
and the running product as: Then from the above equivalence, we can write: This means we can get the running products in SQL using the analytic SUM combined with the (natural) log (LN in Oracle SQL) and exp functions. Let’s see how it works using Oracle’s HR demo schema. We’ll take the employees table and use: * department_id as the partition key * employee_id as the dimension to order by * salary as the measure To start with, let’s get the running and total sums, showing results for department_id = 60: SELECT department_id, employee_id, salary, SUM(salary) OVER (PARTITION BY department_id ORDER BY employee_id) running_sum, SUM(salary) OVER (PARTITION BY department_id) total_sumFROM employees
ORDER BY department_id, employee_id/
Sums
DEPARTMENT_ID EMPLOYEE_ID SALARY RUNNING_SUM TOTAL_SUM ------------- ----------- ---------- ----------- ---------- 60 103 9000 9000 28800 60 104 6000 15000 28800 60 105 4800 19800 28800 60 106 4800 24600 28800 60 107 4200 28800 28800 Next, let’s use the above equivalence to get the running and total products of the expression (1 + salary/10000), which we’ll callmult:
SELECT department_id, employee_id, salary, (1 + salary/10000) mult, EXP(SUM(LN((1 + salary/10000))) OVER (PARTITION BY department_id ORDER BY employee_id)) running_prod, EXP(SUM(LN((1 + salary/10000))) OVER (PARTITION BY department_id)) total_prodFROM employees
ORDER BY department_id, employee_id/
Products
DEPARTMENT_ID EMPLOYEE_ID SALARY MULT RUNNING_PROD TOTAL_PROD ------------- ----------- ---------- ---------- ------------ ---------- 60 103 9000 1.9 1.9 9.45551872 60 104 6000 1.6 3.04 9.45551872 60 105 4800 1.48 4.4992 9.45551872 60 106 4800 1.48 6.658816 9.45551872 60 107 4200 1.42 9.45551872 9.45551872 If we didn’t have this technique we could compute the results using explicit recursion, either by MODEL clause, or by recursive subquery factors. Let’s do it those ways out of interest. First here’s a MODEL clause solution: WITH multipliers AS ( SELECT department_id, employee_id, salary, (1 + salary/10000) mult, COUNT(*) OVER (PARTITION BY department_id) n_empsFROM employees
)
SELECT department_id, employee_id, salary, mult, running_prod, total_prodFROM multipliers
MODEL
PARTITION BY (department_id) DIMENSION BY (Row_Number() OVER (PARTITION BY department_id ORDER BY employee_id) rn) MEASURES (employee_id, salary, mult, mult running_prod, mult total_prod, n_emps)RULES (
running_prod = mult * running_prod, total_prod = running_prod)
ORDER BY department_id, employee_id Finally, here’s a solution using recursive subquery factors: WITH multipliers AS ( SELECT department_id, employee_id, salary, (1 + salary/10000) mult, Row_Number() OVER (PARTITION BY department_id ORDER BY employee_id) rn, COUNT(*) OVER (PARTITION BY department_id) n_empsFROM employees
WHERE department_id = 60 ), rsf (department_id, employee_id, rn, salary, mult, running_prod) AS ( SELECT department_id, employee_id, rn, salary, mult, mult running_prodFROM multipliers
WHERE rn = 1
UNION ALL
SELECT m.department_id, m.employee_id, m.rn, m.salary, m.mult, r.running_prod * m.multFROM rsf r
JOIN multipliers m ON m.rn = r.rn + 1 AND m.department_id = r.department_id)
SELECT department_id, employee_id, salary, mult, running_prod, Last_Value(running_prod) OVER (PARTITION BY department_id) total_prodFROM rsf
ORDER BY department_id, employee_id You can see the scripts and full output on my new GitHub project, Small SQL projects , in the sums_products folder. You can get the full detail on using analytic functions from theOracle doc:
SQL for Analysis and Reporting See also: Analytic and Recursive SQL by ExampleShare24
Posted in Model , Oracle, SQL
, Subquery Factor
| Tagged Analytics
, Oracle
, Recursive
, SQL
, Subquery Factor
| Leave a Reply
POST NAVIGATION
← Older posts
MY BLOG POSTS
* Knapsacks and Networks in SQL * Messages from Outer Space * On RDBMS, SQL and the DRY Principle, and Query Networks * Oracle PL/SQL API Demos Github ModuleMY DESIGN POSTS
* A Layered Approach To Processing XML Web Services * Brendan's 2-Page Oracle Programming Standards * Data Modelling of XML SOAP Documents * Data Structure Diagramming * NoCOUG SQL Challenge 2014 Illustrated* Query Query Query
* Query Structure Diagramming * SQL and Modularity: Patterns, Anti-Patterns and the Kitchen Sink * SQL Developer: Importing Unit Test Repository via Data Modeler MY FUNCTIONAL TESTING POSTS * A Note on Dependencies and Database Unit Testing * A Template Script for JDBC Integration Testing of OracleProcedures
* Design Patterns for Database API Testing 1: Web Service Saving 1– Design
* Design Patterns for Database API Testing 1: Web Service Saving 2– Code
* Design Patterns for Database API Testing 2: Views 1 – Design * Design Patterns for Database API Testing 2: Views 2 – Code * Design Patterns for Database API Testing 3: Batch Loading of FlatFiles
* Design Patterns for Database API Testing 4: REF Cursor Getter * Extracting Pure Functionality from SQL Queries * Oracle and JUnit Data Driven Testing: An Example * TRAPIT – TRansactional API Testing in Oracle MY ORACLE EBUSINESS POSTS * A Design Pattern for Oracle eBusiness Audit Trail Reports with XMLPublisher
* A Generic Unix Script for Uploading Oracle eBusiness ConcurrentPrograms
* Design Patterns for Database Reports with XML Publisher and EmailBursting
MY OTHER SQL POSTS
* A Utility for Reading REF Cursors into a List of Delimited Strings * Case Expressions and Ignoring Nulls in Analytic Functions * Design Patterns for Extracting Relational Data from XML * Grouping by Unique Subsequences in SQL * Holographic Set Matching in SQL (MDTM2) * List Aggregation in Oracle – Comparing Three Methods * Master-Detail Transaction Matching in SQL (MDTM1) * Master-Detail Transaction Reconciliation in SQL (MDTM3) * SQL for Continuum and Contiguity Grouping MY PERFORMANCE TESTING POSTS * A Framework for Dimensional Benchmarking of SQL Query Performance * A Note on Oracle Join Orders and Hints * An Odd Performance Problem with Update – Twitter Thread * Benchmarking of Hash Join Options in SQL for Fixed-DepthHierarchies
* Benchmarking Oracle DML: A Case Study I – Update vs Merge, AnExample
* Benchmarking Oracle DML: A Case Study II – Effects of Indexes * Code Timing and Object Orientation and Zombies * Dimensional Benchmarking of Bracket Parsing SQL * Dimensional Benchmarking of General SQL Bursting Problems * Dimensional Benchmarking of Oracle v10-v12 Queries for SQLBursting Problems
* Dimensional Benchmarking of SQL for Fixed-Depth Hierarchies * Dimensional Benchmarking of String Splitting SQL * Low-Hanging Fruit: The Classic Performance Tuning Anti-pattern * Notes on Profiling Oracle PL/SQLMY PRESENTATIONS
* Creating Open Source Projects for Database Code – Ireland Oracle User Group Conference 2020 * Database API Viewed As A Mathematical Function: Insights into Testing – OUG Ireland Conference 2018 * Dimensional Performance Benchmarking of SQL – IOUG Presentation * Oracle Unit Testing with utPLSQL * Writing Clean Code in PL/SQL & SQL – Ireland Oracle User GroupConference 2019
MY RECURSIVE SQL POSTS * A Note on Running Sums and Products in SQL * A Simple SQL Solution for the Knapsack Problem (SKP-1) * An SQL Solution for the Multiple Knapsack Problem (SKP-m) * Analytic and Recursive SQL by Example * PL/SQL Pipelined Function for Network Analysis * Recursive SQL for Network Analysis, and Duality * SQL for Length-Controlled List Aggregation * SQL for Network Grouping * SQL for Shortest Path Problems * SQL for Shortest Path Problems 2: A Branch and Bound Approach * SQL for the Balanced Number Partitioning Problem * SQL for the Fantasy Football Knapsack Problem * SQL for the Travelling Salesman ProblemMY SCRIBD ARTICLES
* A Database System High Level Design Template * A Perl Object for Flattened Master-Detail Data in Excel * A Structured Approach to SQL Query Design * An Oracle Network Traversal PL SQL Program * Analysing Unix System Performance with prstat and SQL * Batch Migration and Loading of Oracle FND Data * Code Timing and Object Orientation and Zombies * Forming Range-Based Break Groups With Advanced SQL * IT Project Development To Release Processes * Oracle Applications Extension Installation Template * Oracle Applications FNDLOAD Unix Scripts * Oracle Applications Utility SQL Scripts * Oracle Forms Generic UTP * Oracle Multi-Org – AR and OM Data Structure Changes * SQL Pivot and Prune Queries – Keeping an Eye on Performance * Unit Test Plan TemplateMY UNIX POSTS
* Unix Snippets for Handling Duplicate Keys in Delimited Input FilesOTHER BLOGS
* Stew Ashton's Blog GET MORE STUFF LIKE THISIN YOUR INBOX
Subscribe to our mailing list and get interesting stuff and updates toyour email inbox.
I consent to my submitted data being collected via this form* Thank you for subscribing. Something went wrong. we respect your privacy and take protecting it seriously Proudly powered by WordPressDetails
4
Copyright © 2024 ArchiveBay.com. All rights reserved. Terms of Use | Privacy Policy | DMCA | 2021 | Feedback | Advertising | RSS 2.0