Frequency Histograms 2

I find it convenient occasionally to “translate” a frequency histogram into a report of the underlying data (sometimes to see how closely the histogram matches the real data). To demonstrate the type of query I use I’ve created a data set with a small number of distinct values and generated a frequency histogram on the data set. This is what the data and histogram look like:

SQL> desc t1
 Name                    Null?    Type
 ----------------------- -------- ----------
 N1                      NOT NULL NUMBER

SQL> select n1, count(*) from t1 group by n1 order by n1;

        N1   COUNT(*)
---------- ----------
        10          4
        20          3
        30          6
        40         38
        50          4
        60         13
        70          6
        80         41
        90          2
       100          3

SQL> select
  2     endpoint_number, endpoint_value
  3  from
  4     user_tab_histograms
  5  where
  6     table_name  = 'T1'
  7  and        column_name = 'N1'
  8  order
  9     by endpoint_number
 10  ;

ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
              4             10
              7             20
             13             30
             51             40
             55             50
             68             60
             74             70
            115             80
            117             90
            120            100

If you look at the histogram data you’ll see that the “endpoint_value” column holds a list of the values that appear in column n1, and the “endpoint_number” is the ‘cumulative frequency’ for the appearances of the endpoint_value – the number 10 appears 4 times, the number 20 appears 7 – 4 = 3 time, the value 30 appears 13 – 7 = 6 times, and so on.

Once we’ve recognised this pattern we can write some SQL to convert the histogram back to the list of values it represents by using the analytic lag() function. I’ll demonstrate this in two steps – first generating an intermediate result to show the effect of the lag() function.

select
	endpoint_number,
	lag(endpoint_number,1) over(
		order by endpoint_number
	)		delta,
	endpoint_value
from
	user_tab_histograms
where
	table_name  = 'T1'
and	column_name = 'N1'
order by
	endpoint_number
;

ENDPOINT_NUMBER      DELTA ENDPOINT_VALUE
--------------- ---------- --------------
              4                        10
              7          4             20
             13          7             30
             51         13             40
             55         51             50
             68         55             60
             74         68             70
            115         74             80
            117        115             90
            120        117            100

The lag(column, N) function requires an order clause in its over() clause and has the effect of reporting a value N rows later than it would otherwise appear (relative to that ordering).

As soon as we’ve got two consecutive endpoint numbers being reported as two columns in the same row we can find the difference – which gives us the answer we need:

select
	endpoint_value				column_value,
	endpoint_number - nvl(prev_endpoint,0)	frequency
from	(
	select
		endpoint_number,
		lag(endpoint_number,1) over(
			order by endpoint_number
		) 				prev_endpoint,
		endpoint_value
	from
		user_tab_histograms
	where
		table_name  = 'T1'
	and	column_name = 'N1'
	)
order by
	endpoint_number
;

COLUMN_VALUE  FREQUENCY
------------ ----------
          10          4
          20          3
          30          6
          40         38
          50          4
          60         13
          70          6
          80         41
          90          2
         100          3

This is the code for numeric columns; for data columns you simply need to select to_date(to_char(endpoint_value,’J’)) rather than just endpoint_value. (Edit: this works for date-only values, but if you stored date and time then see the correction in comments 3 and 4 and below). Character strings, though are more of a problem in the general case – and I may talk about those in another blog.

Note, by the way, that the mechanism that Oracle uses to generate the histogram data means that the results appear in the same order whether you sort by endpoint_value or endpoint_number. (Unless – as can happen with character columns, a point I initially forget – the same endpoint_value appears in two rows: so I’ve edited the original code where I had used endpoint_value to use endpoint_number)

[Further reading on Histograms]