111–120 of 187 posts
Earlier quoted context omitted.
What if I wrote a very long, complicated query that I'd like to test against different tables (like test tables), and let's say I can't use stored functions or procedures. How could I pass different tables to my query?
If you can't use stored procedures which are good for this very case, many databases offer dynamic SQL. That might work in some cases.
Earlier quoted context omitted.
The challenge I, with limited knowledge, see with developing detailed algorithms in SQL is a lack of good testing, abstraction, and review tooling. Similarly for a lot of the user-defined-functions for the larger data warehouses (redshift, bigQuery, etc.) dbt solves a lot of it, but I'd love to learn more about good resources for building reliable and readily-readable SQL algorithms for complex logic.
I've supported 3 different models over the years with inference implemented in SQL. First one I inherited, loved it so much that I implemented it twice again. Amazingly fast for TBs of data and no waiting on other tech teams. That tooling you're describing is definitely not there. Bigquery has BQML but it's very much in its infancy. I tend to do all the modeling in Python/R on sampled data and then deploy to SQL.
This is great. The only thing I dislike from this is using these variables to try to predict Adj Close when they are not at all correlated. There are countless meaningful correlations in financial data that would have been just as easy to play around with. One truly valuable example would be to look at trading multiples of comparable companies. Sticking to P/E would be easier as P is easily observable and forward-loo…
Earlier quoted context omitted.
-PostgreSQL extensions are easy to include and use. -pgTAP exists for testing. -A large query in SQL is not made smaller but translating it into an ORM DSL. -If "Query" in "SQL" means it's for querying data, then evidently "Query" not being in say Java or Python means those languages are NOT meant for querying data. If that's true, then why would you use them for querying data?
> If "Query" in "SQL" means it's for querying data, then evidently "Query" not being in say Java or Python means those languages are NOT meant for querying data If X then Y does not imply if not X then not Y. Java and Python do not indicate a purpose in their name because they are general-purpose.
Earlier quoted context omitted.
Is a query not a relation?
Basically, but queries are not first class in SQL. You can't assign a query to a variable, or pass it as a parameter to a stored procedure, for example. This would make SQL composable: declare @x = (select * from Person) select Name from @x where Birthdate
Earlier quoted context omitted.
create table x as (select * from person); select name from x where ...; there you go, just configure your editor to display "create table x" as "declare x = " ;) or even a version with lazy evaluation: create view x as (select * from person); select name from x where ...;
What if I wrote a very long, complicated query that I'd like to test against different tables (like test tables), and let's say I can't use stored functions or procedures. How could I pass different tables to my query?
Earlier quoted context omitted.
> Databases are much more powerful than we think The older I get the more I agree with this. There is nothing you cannot build by combining SQL primitives. Side effects can even be introduced - on purpose - by way of UDFs that talk to the outside world. I've seen more than one system where the database itself was directly responsible for things like rendering final HTML for use by the end clients. You might think thi…
> I've seen more than one system where the database itself was directly responsible for things like rendering final HTML for use by the end clients. I did this for a side project a few months ago and even used postgrest to serve the page with correct headers for html. It felt simultaneously really cursed and obvious. Shit you could even use plv8 to run mustache or whatever in the db if you really wanted to piss peopl…
>A machine learning algorithm which can be trained using SQL opens a world of possibilities. The model and the data live in the same space. This is as simple as it gets in terms of architecture. Basically, you only need a database which runs SQL. First paragraph of the conclusion, and this very much fits with the mindset that's been growing in me in the data world over the past few years. Databases are much more powe…
And using PostgREST [0] you can serve your postgreSQL database as REST-API. And if you throw foreign data wrappers / multicorn in the mix, you can map any other datasource into your postgreSQL-db as table.
>A machine learning algorithm which can be trained using SQL opens a world of possibilities. The model and the data live in the same space. This is as simple as it gets in terms of architecture. Basically, you only need a database which runs SQL. First paragraph of the conclusion, and this very much fits with the mindset that's been growing in me in the data world over the past few years. Databases are much more powe…
Fully agree. And using PostgREST [0] you can serve your postgreSQL database as REST-API. And if you throw foreign data wrappers / multicorn in the mix, you can map any other datasource into your postgreSQL-db as table. [0] https://postgrest.org/en/stable/