44 lines
937 B
SQL
44 lines
937 B
SQL
set verify off
|
|
|
|
col value head 'Value'
|
|
col f1 format 999,999,999 head 'Table #1 | frequency'
|
|
col f2 format 999,999,999 head 'Table #2 | frequency'
|
|
column product format 999,999,999 head 'Tables join | frequency | (product)'
|
|
|
|
|
|
break on report skip 1
|
|
compute sum of product on report
|
|
|
|
WITH f1
|
|
AS (
|
|
SELECT endpoint_value value
|
|
,endpoint_number - lag(endpoint_number, 1, 0) OVER (
|
|
ORDER BY endpoint_number
|
|
) frequency
|
|
FROM dba_tab_histograms
|
|
WHERE OWNER = '&1'
|
|
AND table_name = '&2'
|
|
AND column_name = '&3'
|
|
ORDER BY endpoint_value
|
|
)
|
|
,f2
|
|
AS (
|
|
SELECT endpoint_value value
|
|
,endpoint_number - lag(endpoint_number, 1, 0) OVER (
|
|
ORDER BY endpoint_number
|
|
) frequency
|
|
FROM dba_tab_histograms
|
|
WHERE OWNER = '&4'
|
|
AND table_name = '&5'
|
|
AND column_name = '&6'
|
|
ORDER BY endpoint_value
|
|
)
|
|
SELECT f1.value
|
|
,f1.frequency f1
|
|
,f2.frequency f2
|
|
,f1.frequency * f2.frequency product
|
|
FROM f1
|
|
,f2
|
|
WHERE f2.value = f1.value;
|
|
|