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

Stochastic gradient descent written in SQL

maxhalford.github.io

Post reply on HN
Print view View original

91–100 of 187 posts

Re: Stochastic gradient descent written in SQL

#91

Post by Lemaxoxo »

Terretta wrote: post #86

Title here is wrong. Title in article and headings in article are right: ONLINE gradient descent It's specifically not stochastic. From the article: Online gradient descent Finally, we have enough experience to implement online gradient descent. To keep things simple, we will use a very vanilla version: - Constant learning rate, as opposed to a schedule. - Single epoch, we only do one pass on the data. - Not stochast…

Hehe I was wondering if someone would catch that. Rest assured, I know the difference between online and stochastic gradient descent. I admit I used stochastic on Hacker News because I thought it would generate more engagement.

Re: Stochastic gradient descent written in SQL

#92

Post by alphanumeric0 »

chrstr wrote: post #85
naasking wrote: post #77

Earlier quoted context omitted.

Where is the assignment to a variable? Where can you construct a query using a variable in table/query position? That's the whole point of being first class and composable, a query becomes like any other value so you should be able to parameterize any query by another query assigned to a variable that may have been set inside an if-statement, or accepted as a parameter to a stored procedure. You know, the same kinds…

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?

Re: Stochastic gradient descent written in SQL

#93

Post by dventimi »

syats wrote: post #66
dventimi wrote: post #49

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?

Re modules/libraries: I meant it is not easy to write a piece of SQL code, and then import it into several queries to reuse it, or lend it to someone else for use on their on schema. It is possible, yes, but seldom done, because it is hell. PostgreSQL extensions could be used for this purpose, but developing an extension requires a different set of SQL statements (or luckily, python or c) than those used by the user…

> maybe that will change one day

I pray that it never does.

https://blog.codinghorror.com/object-relational-mapping-is-t...

Re: Stochastic gradient descent written in SQL

#94

Post by dventimi »

syats wrote: post #66
dventimi wrote: post #49

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?

Re modules/libraries: I meant it is not easy to write a piece of SQL code, and then import it into several queries to reuse it, or lend it to someone else for use on their on schema. It is possible, yes, but seldom done, because it is hell. PostgreSQL extensions could be used for this purpose, but developing an extension requires a different set of SQL statements (or luckily, python or c) than those used by the user…

As for sharing SQL, that's easy to do within a database using views. Across databases with possibly different data models, that's not something I personally ever want to do.

Re: Stochastic gradient descent written in SQL

#95

Post by guhidalg »

krick wrote: post #52

From the start I assumed this is a nice playful "Hexing the technical interview" kinda joke. But given the tone of the article, and some of the comments here… Uh, this cannot be serious, right?

Why couldn't it be serious? Your database already has your data so you don't have to stream it to another machine for training. If you have a developed model that needs retraining on new data, save yourself the network bandwidth (and time!) and just make the DB retrain. This doesn't replace the exploration phase of model-building but if you already know gradient descent works for you then what's wrong with this approach?

Re: Stochastic gradient descent written in SQL

#96

Post by dventimi »

alphanumeric0 wrote: post #92
chrstr wrote: post #85

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?

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.

Re: Stochastic gradient descent written in SQL

#97

Post by adeelk93 »

alphanumeric0 wrote: post #92
chrstr wrote: post #85

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?

Check out dbt - it's a great tool for organizing queries and solving such patterns

Re: Stochastic gradient descent written in SQL

#98

Post by giraffe_lady »

bob1029 wrote: post #80
jackschultz wrote: post #13

>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…

> 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 people off.

Re: Stochastic gradient descent written in SQL

#99

Post by 7thaccount »

jackschultz wrote: post #13

>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…

Database first designs make a lot of sense in a lot of ways. I've worked for a company with an Oracle database that has SQL scripts that do all the validation and create text files for downstream usage. I think it makes more sense than a ton of Java, but there are pros and cons. One is that SQL is relational and the advanced stuff can be extra hard to troubleshoot if you don't have enough experience. Even those that can't code can usually understand a for loop and can think imperatively.

Unfortunately it's an expensive commercial product or I'd recommend you look at kdb+ if you work with time series data. The big banks use it and essentially put all thier latest RT data into kdb+ and then can write extremely succinct queries with a SQL-like syntax, but the ability to approach it far more programmatically than what is typically doable with something like PL-SQL. You can even write your neural network or whatever code in less than a page of code as the language of kdb+ is extremely powerful, although also basically incomprehensible until someone puts some time into learning it. It's extremely lightweight though, so very easy to deal with in an interactive fashion.

All that to say I agree with you that it's nice to just have everything you want all in one spot rather than to deal with 4 different tools and pipelines and shared drives and so on.

Re: Stochastic gradient descent written in SQL

#100

Post by chrstr »

alphanumeric0 wrote: post #92
chrstr wrote: post #85

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?

  CREATE TABLE data_a AS (SELECT 'a' AS test_case, 1 AS value);
  CREATE TABLE data_b AS (SELECT 'b' AS test_case, 2 AS value);
  CREATE VIEW data AS (SELECT * FROM data_a UNION ALL SELECT * FROM data_b);

  CREATE VIEW complicated_query AS (SELECT test_case, value+1 FROM data);

  SELECT * FROM complicated_query WHERE test_case = 'a';
  SELECT * FROM complicated_query WHERE test_case = 'b';
Post reply on HN