3 ms·

This is an accurate description of how I think of this problem. And it would be tough to describe all this: - table a joins b yay a view - table b joins c yay

This is an accurate description of how I think of this problem. And it would be tough to describe all this: - table a joins b yay a view - table b joins c yay a view - table c join d yay a view - table a joins to d. well technically it can, but are you really gonna write all the permutations of views for every possible join? ORMs encode that nicely so I can easily walk the relationships and get to the query I need.

dragonwriter 4y ago
Essentially all of the actual information is encoded in the foreign key constraints (in combination with uniqueness constraints.) What ORMs provide that SQL doesn’t isn’t much encoding of information (they usually have facilities to distinguish data tables from pure join tables, and to distinguish 1:1 and 1:M, (M>0) relations from 1:(0-1) and 1:M, (M>=0) ones, so they do encode some additional information), but provide convenient syntactic shorthands for the client to use the encoded relationships. This would be easy to add to SQL, as syntax sugar, https://news.ycombinator.com/item?id=34587412 https://news.ycombinator.com/item?id=34587412
- Justsignedup 4y ago
  To be completely honest, I forgot who was it, but about a year or 2 ago someone posted about a sql alternative language. The goal was to be adaptable to be used in all sql application, but a better designed language so that things like a calculation in a select can be reused in another select column, and better structure to make things easily reusable or composable, etc. Just a new language to replace sql, that is still sql but just better designed with the benefit of 30+ years of language design improvements.
  - ntonozzi 4y ago
    Was it PRQL? That’s what all of these comments remind me of. https://prql-lang.org/ https://prql-lang.org/
  - zX41ZdbW 4y ago
    There were countless attempts to extend or replace SQL: OQL: https://en.wikipedia.org/wiki/Object_Query_Language https://en.wikipedia.org/wiki/Object_Query_Language UnQL: https://www.dataversity.net/unql-a-standardized-query-language-for-nosql-databases/ https://www.dataversity.net/unql-a-standardized-query-langua... More modern: PRQL: https://prql-lang.org/ https://prql-lang.org/ Malloy: https://news.ycombinator.com/item?id=30053860 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 window functions, SQL/JSON, and extends it to make it convenient for data analysts by adding: - higher-order functions; nested data structures, arrays, tuples, and maps; aggregate function states as first-class citizens, unrestricted usage of aliases in any place of expression, etc. https://github.com/ClickHouse/ClickHouse/ https://github.com/ClickHouse/ClickHouse/ I'm an everyday user of ClickHouse, and I'm finding its SQL implementation the most pleasant to use! Although it's unsurprising, because I'm also one of its authors... I'm also welcoming innovation and improvement of SQL without the introduction of a completely different language.
    - jrumbut 4y ago
      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 a few times per day. Currently I analyze this data in Python, R, and sometimes SAS (a weird proprietary language). Some coworkers use Matlab. Is that a ClickHouse problem? If I tried it out would the ClickHouse community be interested in hearing how it goes?
      - zX41ZdbW 4y ago
        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

dragonwriter 4y ago

Essentially all of the actual information is encoded in the foreign key constraints (in combination with uniqueness constraints.) What ORMs provide that SQL doesn’t isn’t much encoding of information (they usually have facilities to distinguish data tables from pure join tables, and to distinguish 1:1 and 1:M, (M>0) relations from 1:(0-1) and 1:M, (M>=0) ones, so they do encode some additional information), but provide convenient syntactic shorthands for the client to use the encoded relationships. This would be easy to add to SQL, as syntax sugar, https://news.ycombinator.com/item?id=34587412 https://news.ycombinator.com/item?id=34587412

Justsignedup 4y ago
To be completely honest, I forgot who was it, but about a year or 2 ago someone posted about a sql alternative language. The goal was to be adaptable to be used in all sql application, but a better designed language so that things like a calculation in a select can be reused in another select column, and better structure to make things easily reusable or composable, etc. Just a new language to replace sql, that is still sql but just better designed with the benefit of 30+ years of language design improvements.
- ntonozzi 4y ago
  Was it PRQL? That’s what all of these comments remind me of. https://prql-lang.org/ https://prql-lang.org/
- zX41ZdbW 4y ago
  There were countless attempts to extend or replace SQL: OQL: https://en.wikipedia.org/wiki/Object_Query_Language https://en.wikipedia.org/wiki/Object_Query_Language UnQL: https://www.dataversity.net/unql-a-standardized-query-language-for-nosql-databases/ https://www.dataversity.net/unql-a-standardized-query-langua... More modern: PRQL: https://prql-lang.org/ https://prql-lang.org/ Malloy: https://news.ycombinator.com/item?id=30053860 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 window functions, SQL/JSON, and extends it to make it convenient for data analysts by adding: - higher-order functions; nested data structures, arrays, tuples, and maps; aggregate function states as first-class citizens, unrestricted usage of aliases in any place of expression, etc. https://github.com/ClickHouse/ClickHouse/ https://github.com/ClickHouse/ClickHouse/ I'm an everyday user of ClickHouse, and I'm finding its SQL implementation the most pleasant to use! Although it's unsurprising, because I'm also one of its authors... I'm also welcoming innovation and improvement of SQL without the introduction of a completely different language.
  - jrumbut 4y ago
    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 a few times per day. Currently I analyze this data in Python, R, and sometimes SAS (a weird proprietary language). Some coworkers use Matlab. Is that a ClickHouse problem? If I tried it out would the ClickHouse community be interested in hearing how it goes?
    - zX41ZdbW 4y ago
      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