The simple example of computing an average over a single column makes it seem like each columns data is stored by itself, but as soon as you want to do something with the correlation of two columns (such as a simple single column group by) doesnt that mean that each column would have to store both the column's value and some kind of row identifier that lets you join with the other columns in the same record? I've never looked into columnar stores so this is an honest question, I just always assumed thats how it would have to be structured
You correlate positionally, within a block/rowgroup.
If you run e.g. a `SELECT SUM(col1) WHERE col2 = x`, a columnar DB will typically produce produce a bitmap from of col2, use it to mask col1, then sum what's left—all very efficient operations.