self joins

Community Support
1

I’m try to convert some SQL over to q and it has a couple of self
joins in it.
I didn’t find any examples online involving q self joins. The SQL is:

select ts.tid, ts.sensor_id, ts.recorded_value, k.avgval, k.stddev,
ts.ts_milliseconds
from t_final ts
join (select stats.tid, stats.sensor_id, avg(stats.recorded_value)
as avgval,
stddev_pop(stats.recorded_value) as stddev,
max(stats.ts_milliseconds) as ts_max
from t_final stats
join (select tid, sensor_id, max(ts_milliseconds) as ts_max
from t_final
group by tid, sensor_id) j
on j.tid=stats.tid and j.sensor_id=stats.sensor_id
where (stats.ts_milliseconds>=j.ts_max - (120000))
group by stats.tid, stats.sensor_id) k
on ts.tid=k.tid and ts.sensor_id=k.sensor_id and
ts.ts_milliseconds=k.ts_max
where (ts.recorded_value > k.avgval + k.stddev) or
(ts.recorded_value < k.avgval - k.stddev)

where the t_final table is:

t_final: ( tid:symbol$(); sensor_id:symbol$();
recorded_value:float$(); ts_milliseconds:long$())

How are the self joins handled? Thanks.
Barry

2

There is no native self join in kdb, here is a version of your queryin qinner:0!select avgval:avg(recorded_value),stddev:dev(recorded_value),ts_max: max(ts_milliseconds) by tid,sensor_id fromej[tidsensor_id;t_final;0!select ts_max:max(ts_milliseconds) bytid,sensor_id from t_final] where ts_milliseconds>= ts_max-120000;inner:((-1_cols inner),ts_milliseconds) xcol inner;select from ej[tidsensor_idts_milliseconds;t_final;inner] where(recorded_value> (avgval+stddev) | recorded_value < (avgval-stddev));On Nov 11, 1:02?am, Barry Hathaway <barry.r.hatha…> wrote:> I’m try to convert some SQL over to q and it has a couple of self> joins in it.> I didn’t find any examples online involving q self joins. ?The SQL is:>> select ts.tid, ts.sensor_id, ts.recorded_value, k.avgval, k.stddev,> ts.ts_milliseconds> ? from t_final ts> ? join (select stats.tid, stats.sensor_id, avg(stats.recorded_value)> as avgval,> ? ? ? ? ? ? ? ?stddev_pop(stats.recorded_value) as stddev,> max(stats.ts_milliseconds) as ts_max> ? ? ? ? ? from t_final stats> ? ? ? ? ? join (select tid, sensor_id, max(ts_milliseconds) as ts_max> ? ? ? ? ? ? ? ? ? from t_final> ? ? ? ? ? ? ? ? ? group by tid, sensor_id) j> ? ? ? ? ? on j.tid=stats.tid and j.sensor_id=stats.sensor_id> ? ? ? ? ? where (stats.ts_milliseconds>=j.ts_max - (120000))> ? ? ? ? ? group by stats.tid, stats.sensor_id) k> ? on ts.tid=k.tid and ts.sensor_id=k.sensor_id and> ts.ts_milliseconds=k.ts_max> ? where (ts.recorded_value > k.avgval + k.stddev) or> (ts.recorded_value < k.avgval - k.stddev)>> where the t_final table is:>> t_final: ( tid:symbol$(); sensor_id:symbol$();> recorded_value:float$(); ts_milliseconds:long$())>> How are the self joins handled? Thanks.> Barry</barry.r.hatha…>

3

Ajay, thanks for the help with the query. I just have a quickquestion.Does the “inner:” assignment make of copy of the t_final table andcould this be a problem for a large table size?

4

kdb doesnt copy all the column vectors on reassignment, so memoryconsumption will increase only slightlyOn Nov 11, 10:05?pm, Barry Hathaway <barry.r.hatha…>wrote:> Ajay, thanks for the help with the query. I just have a quick> question.> Does the “inner:” assignment make of copy of the t_final table and> could this be a problem for a large table size?</barry.r.hatha…>