I'm a novice at modern SQL, and am pretty sure I'm over-complicating something. What I'm trying to do is break down values into percentiles, both by the values themselves and by their frequency. So if I've got 1,000 records with 100 distinct numbers, there's a range of values, and also a range of frequencies with which those values occur. What I want is to get for each value is
- The value itself.
- Its percentile within the range of values
- It's count (frequency)
- The percentile of the frequency
I'm using toy tables to experiment on with 1,000 records populated with random-ish values from www.mockaroo.com. My real tables have 100s of thousands or millions of rows. The point of all of this is to bolt the percentiles, etc. onto the end of each row in a view to feed a data visualization platform that isn't great at percentiles. To make it clear, if I'm starting with 1K records in my table, the queries should end up with 1K rows.
CREATE TABLE IF NOT EXISTS mock (
n integer
);
Given my tiny row count in the toy tables, I'm using deciles, not percentiles, but that shouldn't change anything about how the searches are structured. Here's how I'm getting what I need from the one-column table:
-- Get the value, count, and value percentile.
with
value_counts as (
select n as value,
count(*) as frequency_count,
ntile(10) over (order by n) as value_decile
from mock
group by n
),
-- Now add the frequency percentile.
frequency_analysis as (
select value,
ntile(10) over (order by frequency_count) as frequency_decile
from value_counts
),
-- Don't need this CTE, just making things readable.
value_information as (
select value_counts.value,
value_counts.value_decile,
value_counts.frequency_count,
frequency_analysis.frequency_decile
from value_counts
join frequency_analysis on (frequency_analysis.value= value_counts.value)
)
select * from value_information;
So, I think that's working....but I don't just have one column to check, I've got a lots of columns to generate frequency count, frequency percentile, and value percentiles on. This seems like it should be a commonplace sort of statistical query to run, but I'm finding it hard to figure out how to do it neatly in Postgres. A couple of points before getting into a two-column table:
I'm using the CTEs for legibility, to create aggregates that can then be aggregated in a later CTE, and to produce a small product that can be joined against. Everything is fast with 1K records, but maybe not so much with 20M records.
I'm on Postgres 11 and won't move to PG 12 until some time after it ships. So, there's no concern yet on how CTEs are materialized. The PG 11 behavior is exactly what I think I want.
I'm using ntile() because it's really simple to adjust the number of bins dynamically, so 10 instead of 100. I'm not sure of if I should be using width_bucket, percentile_cont/percentile_disc instead. I just found width_bucket this morning, so maybe there's another built-in way to do percentiles. (Apart from hand-coding it.)
I'm completely open to throwing out all of this and going something else, if there's a better way. That's why I'm experimenting with toy data to start with.
Okay, now an example of a more realistic table with more meaningful field names:
CREATE TABLE IF NOT EXISTS ascendco.mock2 (
num_inst integer,
points integer
);
Again, 1,000 records with different values in the two columns. The two columns have no relationship in the calculations, they're just both in the same row, and I want the aggregations added to the end. It all sounds very much like a window function kind of operation, but I don't want to iterate the work over 10M rows. So, how to do this for two+ columns? I have 2-5 in most of my tables today, and that will grow. What I'm running into is that GROUP BY is meant to give one level of aggregation, I need a distinct aggregation on each column. Is there a way to do this other than the long form I've tried out below?
-- Get the num_inst count and the decile for the value.
with
num_inst_distinct_counts as (
select num_inst,
count(*) as num_inst_frequency,
ntile(10) over (order by num_inst) as value_decile
from mock2
group by num_inst),
-- Extend the previous CTE with the decile for the value's frequency.
num_inst_information as (
select *,
ntile(10) over (order by num_inst_frequency) as frequency_decile
from num_inst_distinct_counts
),
-- Get the points count and the decile for the value.
points_distinct_counts as (
select points,
count(*) as points_frequency,
ntile(10) over (order by points) as value_decile
from mock2
group by points),
-- Extend the previous CTE with the decile for the value's frequency.
points_information as (
select *,
ntile(10) over (order by points_frequency) as frequency_decile
from points_distinct_counts
)
-- Put it all togehter. I could have used more general names in the CTEs, but this makes the output clearer
select mock2.num_inst,
num_inst_information.value_decile as num_inst_value_decile,
num_inst_information.frequency_decile as num_inst_frequency_decile,
mock2.points,
points_information.value_decile as points_value_decile,
points_information.frequency_decile as points_frequency_decile
from mock2
join num_inst_information on (num_inst_information.num_inst = mock2.num_inst)
join points_information on (points_information.points = mock2.points)
order by 1;
That just strikes me as crazy long and involved. I'm guessing that there's some tidy way to use an array an a LATERAL join to get the job done.
Thanks for any help!
Woke up and gave it another go. Well, I didn't understand something about count(*) and such. You can nest aggregates, at least a bit. The revised programs below come up with the same results as the originals, so that's a bit of an improvement. I'm still intuiting that I'm missing something obvious that could make all of this massively simpler. Any ideas? For the record, here are new versions of the one and two-column queries. Those long ORDER BY statements at the bottom of each are there only so that I could grab-and-diff the output of the original and revised queries easily.
Here's the new one-column query, much shorter:
select distinct n as value,
ntile(10) over (order by n) as value_decile,
count(*) as frequency_count,
ntile(10) over (order by count(*)) as frequency_decile
from mock
group by n
order by 1,2,3,4
The two-column version uses one CTE per column and then joins everything together with the master table.
-- Get the details for the num_inst column.
with
num_inst_information as (
select distinct num_inst,
ntile(10) over (order by num_inst) as value_decile,
count(*) as frequency_count,
ntile(10) over (order by count(num_inst)) as frequency_decile
from mock2
group by num_inst
),
-- Get the details for the points column.
points_information as (
select distinct points,
ntile(10) over (order by points) as value_decile,
count(*) as frequency_count,
ntile(10) over (order by count(points)) as frequency_decile
from mock2
group by points
)
-- Get every row in the base table and use the CTEs above for lookups (joins) with the extra data.
select mock2.num_inst,
num_inst_information.value_decile as num_inst_value_decile,
num_inst_information.frequency_decile num_inst_frequency_decile,
mock2.points,
points_information.value_decile as points_value_decile,
points_information.frequency_decile as points_frequency_decile
from mock2
left join num_inst_information on (num_inst_information.num_inst = mock2.num_inst)
left join points_information on (points_information.points = mock2.points)
order by 1,2,3,4,5,6
The new versions are a bit faster, which is nice too.
S-Man asked for some sample data and output. Fair enough! And thanks for reading and want to help. I've set up a Pastebin account with samples of the 1 column
https://pastebin.com/embed_js/eUZkBqhA
and 2-column data: https://pastebin.com/embed_js/7J4vx850
But, honestly, they're just random numbers. The goal of my question is to figure out a concise and efficient way to add derived overall data to each row:
Get the original value from each record in the output. 1M rows in the table, 1M rows in the result.
For each row, add three new columns to the output per "real" column of data:
1) The percentile of the value. 2) The frequency of the value (count.) 3) The percentile of the frequency.
So, for example,
num_inst The real data from the base table.
num_inst_value_percentile The percentile (deciles used above, noted) for that num_inst amongst all num_inst in the table.
num_inst_frequency How frequently does this value appear in the entire table? So, Count.
num_inst_frequency_percentile The percentile (deciles used above, noted) for that num_inst_frequency amongst all num_inst_frequencies in the table.
...and then the same for the base points field, and many, many others in several tables.
In case anyone is wondering, our data is pretty long-tailed and hard to chart. Binning the data into percentiles makes it much easier to figure out the shape of the data/distributions. After I finish with this, the next step is to figure out how to use window functions (I guess) to get the range sizes for each percentile.
Hopefully that's clearer!