Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

It's not you, it's SQL

stack.convex.dev

Post reply on HN
Print view View original

81–84 of 84 posts

Re: It's not you, it's SQL

#81

Post by zX41ZdbW »

jrumbut wrote: post #79
zX41ZdbW wrote: post #78

Earlier quoted context omitted.

There were countless attempts to extend or replace SQL: OQL: https://en.wikipedia.org/wiki/Object_Query_Language UnQL: https://www.dataversity.net/unql-a-standardized-query-langua... More modern: PRQL: https://prql-lang.org/ Malloy: https://news.ycombinator.com/item?id=30053860 (it is so obscure that Google replaces it to "Malay language") Another example: ClickHouse supports standard SQL with many features such as w…

ClickHouse is one of those technologies I've had half an eye on for a while. In your completely unbiased opinion (I kid), do you think it's a good choice for the following problem? I have multiple sensors that read different types of data about the same subject at (annoyingly) slightly different intervals, usually a few dozen times a second. This needs to be combined with other event data that happens on the order of…

Looks like a good scenario for ClickHouse.

One option is to just record all the measurements with the corresponding time. Something like a table with:

  sensor_id, time, value
To align and correlate the measurements, simply round down the time to some bucket. Do something like

  SELECT toStartOfMinute(time) AS t, anyIf(value, sensor_id = 'X'), anyIf(value, sensor_id = 'Y')
  FROM measurements
  WHERE sensor_id IN ('X', 'Y')
  GROUP BY t
  ORDER BY t
Yes, I'm interested in, how it will go! milovidov@clickhouse.com

Re: It's not you, it's SQL

#82

Post by zX41ZdbW »

zX41ZdbW wrote: post #81
jrumbut wrote: post #79

Earlier quoted context omitted.

ClickHouse is one of those technologies I've had half an eye on for a while. In your completely unbiased opinion (I kid), do you think it's a good choice for the following problem? I have multiple sensors that read different types of data about the same subject at (annoyingly) slightly different intervals, usually a few dozen times a second. This needs to be combined with other event data that happens on the order of…

Looks like a good scenario for ClickHouse. One option is to just record all the measurements with the corresponding time. Something like a table with: sensor_id, time, value To align and correlate the measurements, simply round down the time to some bucket. Do something like SELECT toStartOfMinute(time) AS t, anyIf(value, sensor_id = 'X'), anyIf(value, sensor_id = 'Y') FROM measurements WHERE sensor_id IN ('X', 'Y')…

Another interesting option for correlation of measurements at uneven intervals is - using ASOF JOIN.

Re: It's not you, it's SQL

#83

Post by bambax »

This example is really curious:

    BEGIN;
    SELECT post_count, ... from users where ... FOR UPDATE;
    INSERT INTO posts VALUES(...);
    UPDATE users SET post_count = new_post_count WHERE ...;
    COMMIT;
For one, it's unlikely the number of posts per user is so important and so often requested that it needs to be cached in the database itself.

Secondly, why would that value need to be stored with each new insert? Simply insert posts as they come, and calculate metadata about posts at a later time as a batch process, or when some part of the application actually request them.

Re: It's not you, it's SQL

#84

Post by bambax »

jrumbut wrote: post #59
civilized wrote: post #49

Earlier quoted context omitted.

But a view also does that? Like, if you want to assemble information about a user from several different tables, you can have a view that does the join for you.

Sure, but people never use those. Also it gets back to the discoverability issue. The ORM documents relationships with the rest of the model code, if you have a poorly named view in a large, complex schema it may be hard to find. You could reinvent the wheel easily. Like everything with SQL, you can solve the problem but sometimes the solution isn't elegant. People want elegance.

> Sure, but people never use those

Why not? I use views all the time, and they seem pretty elegant and simple to me.

Post reply on HN