Fear No SQL
I like Chess, I like Math, and I like PL/SQL – implementing a chess rating algorithm in pl/sql satisfies three itches in one. The Glicko system was invented by Dr. Mark Glickman in 1995 as a way of rating players and it, along with its successor Glicko-2, have been adopted in a variety of online…
After completing my article about skewness of a sample for Joel Kallman Day I decided I could do one more and write up the related functionality for skewness of a population. Like skewness of a sample, the purpose of the function is to indicate the degree and direction of asymmetry in a data distribution. Like…
Several years ago I needed to calculate the skewness of some data. That is, an indication of how asymmetric points are within a distribution. Unfortunately, I found there was no native feature to do this within SQL. So I wrote my own with the Oracle Data Cartridge Interface (ODCI.) Using ODCI you can create your…
Calculating factorials quickly explode into large results. The SQL and PL/SQL numeric types can’t hold the extremely large values generated by factorial computation of even relatively small numbers. Since several numeric types are subtypes, they inherit the limits of their parent type. All of the types will eventually overflow when the results get too big.…
If you have a column of values, Oracle provides functions to find each of these values: AVG() for the mean (average), MEDIAN() for the median, and STATS_MODE for the mode. STATS_MODE came in 10g. Before that the mode is also possible but requires extra steps for counting and sorting. For example, given the values 1,…
When an object type is instantiated (i.e. assigned a non-null value) it will execute initialization code. This initialization code is called a CONSTRUCTOR function. Every object type includes an implicit constructor which simply assigns values to the object attributes according to the values provided in the object assignment. For example: CREATE OR REPLACE TYPE simpleobject…
I ran into an interesting bug in some code today due to a math error which caused CHR() to be called with non-integer input values. According to the Oracle documentation, this is perfectly acceptable; but it doesn’t make much sense. Unfortunately, the documentation doesn’t specify what should happen for such inputs. This ambiguity is the…
If A < B and B < C then A < C Right? We all know the transitive rule… all of us except the Oracle optimizer. Here’s a seasonal example to illustrate how the Optimizer changes it’s methods based on seemingly trivial options. Let’s say I bake 10 types of cookies and then I want…
Using the algorithm discovered by David Bailey, Peter Borwein, and Simon Plouffe, you can easily generate values of pi with a simple recursive query… SQL> SELECT d, to_char(bbp, rpad(‘0.’,55, ‘9’)) bbp, 2 TO_CHAR( 3 SUM(bbp) OVER (ORDER BY d), 4 ‘9.’ || RPAD(‘9’, d – 1, ‘9’) 5 ) pi …