Query improvement in Oracle SQL

Question

I need to write a hard query, and i need some help.

I have the following table (MyTable)

C1 int,
C2 int,
C3 int,
.
.
C79 int
C80 int

Please note: These columns names are fake. The real columns names are not similar. each column has it's own name which is not similar to any other column.

I have the following query (queryA):

select cnt / (select count(*) from MyTable)
  from (
    select c1, c2, count(*) over(partition by c1, c2) cnt
     from MyTable);

I need to run this query for each sequential columns: C(i) - C(i+1), C(i+1) - C(i+2)...In the query above, i ran it for c1 and c2.

In addition, i have two more similar queries such as the query from above. In the output table, each sequential columns will have 3 calculation from 3 queries.

The output table supposed to look like that (all of the column types supposed to be float):

c12A - calculation of query A for c1-c2
c12B - calculation of query B for c1-c2
c12C - calculation of query C for c1-c2
c23A - calculation of query A for c2-c3
c23B - calculation of query B for c2-c3
c23C - calculation of query C for c2-c3
.
.
.
c7980A - calculation of query A for c79-c80
c7980B - calculation of query B for c79-c80
c7980C - calculation of query C for c79-c80

I hope was clear enough. Any recommendation how to do that?

Answer 1

WITH t1 AS (
SELECT 'MYTABLE' nm FROM dual
),
t2 AS (
select rw, c1, c2 from (
    SELECT column_id rw, column_name c1, 
           lead(column_name) OVER(ORDER BY column_id) c2 FROM cols CROSS JOIN t1 
    WHERE table_name = t1.nm
  ) WHERE c2 IS NOT NULL
)
SELECT 0 rw, 'select rw,n/n_all from (select count(*) n_all from ' || nm || ') cross join ('  FROM t1
UNION ALL
SELECT rw, CASE WHEN rw > 1 THEN 'union all ' END || 'select ' || rw 
           || ' rw, count(*) over(partition by ' || c1 || ', ' || c2 || ') n from ' || t1.nm 
FROM t2 CROSS JOIN t1
UNION ALL
SELECT 10000 rw, ') order by rw'  FROM dual
order by rw;  

You can generate the query string using Oracle dictionary cols (= user_tab_columns).

I used cols.column_id to define the "order" of the columns: lead(column_name) OVER(ORDER BY column_id)