Tom Luo

This example returns the average age of customers who are likely to use an affinity card.

SELECT gender, COUNT(*) AS cnt, ROUND(AVG(age)) AS avg_age
FROM mining_data
WHERE PREDICTION(DT_SH_Clas_sample COST MODEL
USING marital_status, education, household_size) = 1
# The PREDICTION function takes into account only the marital_status, education, and household_size predictors.
GROUP BY gender
ORDER BY gender;

C CNT AVG_AGE
- ———- ———-
F 170 38
M 685 42

RATIO_TO_REPORT is an analytic function. It computes the ratio of a value to the sum of a set of values. If expr evaluates to null, then the ratio-to-report value also evaluates to null.

SELECT ename, sal, ratio_to_report(sal) over (partition by deptno)
FROM emp
It returns the employee name, salary, and the ration of the salary to the sum salary of all employees partitioned by department no.

WIDTH_BUCKET lets you construct equiwidth histograms, in which the histogram range is divided into intervals that have identical size

SELECT customer_id, cust_last_name, credit_limit,
WIDTH_BUCKET(credit_limit, 100, 5000, 10) “Credit Group”
FROM customers WHERE nls_territory = ‘Canada’
ORDER BY “Credit Group”;

Soundex algorithm:
1. Keep the first letter.
2. Convert the remaining letters to digits, according to mapping below:
abcdefghijklmnopqrstuvwxyz => 91239129922455912623919292

The phonetic representation is defined in The Art of Computer Programming, Volume 3: Sorting and Searching, by Donald E. Knuth, as follows:

[From Oracle 10g documentation]
Retain the first letter of the string and remove all other occurrences of the following letters: a, e, h, i, o, u, w, y.

Assign numbers to the remaining letters (after the first) as follows:

b, f, p, v = 1
c, g, j, k, q, s, x, z = 2
d, t = 3
l = 4
m, n = 5
r = 6

If two or more letters with the same number were adjacent in the original name (before step 1), or adjacent except for any intervening h and w, then omit all but the first.

Return the first four bytes padded with 0.

3. Remove consecutive duplicates.
4. Remove all 9s.
5. If the result is shorter than four characters (the first letter plus three digits), pad the result with trailing zeros.
6. if the result is longer than four characters, discard everything after the fourth character.

import string
charToSoundex = string.maketrans(string.letters, "91239129922455912623919292" * 2)

def soundex(source):
if not isalpha(source):
return "0000"
s = source[0].upper() + source[1:].translate(charToSoundex)
s2 = s[0]
for d in s[1:]:
if s2[−1] != d:
   s2 += d
   s3 = re.sub('9', '', s2)
while len(s3) < 4:
   s3 += "0"
return s3[:4]