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

101–110 of 187 posts

Re: Stochastic gradient descent written in SQL

#101

Post by maCDzP »

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…

I agree. I took a course in databases and SQL and was blown away by its power. With CTE’s and PLSQL you can do a lot of stuff inside the database.

I played with SQLite and it’s json columns. Once you get the hang of the syntax for walking a json structure you can do all sorts of neat things. Doing the same thing in Python would be tedious.

And I also believe it ended up being way faster than what I did in python.

Re: Stochastic gradient descent written in SQL

#102

Post by Lemaxoxo »

simonw wrote: post #65

I tried to replicate this in SQLite. The first few steps worked OK, e.g. https://lite.datasette.io/?json=https://gist.github.com/simo... (I replaced "figures" with "raw" due to the way Datasette Lite assigns a default table name to the imported JSON) But the more complex recursive queries gave me this error and I'm not sure how to work around it: recursive reference in a subquery: state E.g. https://lite.datasette.io…

That's too bad, I would have expected it to work out of the box. Other than rewriting the query in a different way, I'm not sure I see an easy workaround. Are you still working on this?

Re: Stochastic gradient descent written in SQL

#103

Post by sgu999 »

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?

I'm very confused as well. Are we facing a wall of GPT-generated comments?

Re: Stochastic gradient descent written in SQL

#105

Post by nfw2 »

dventimi wrote: post #49
syats wrote: post #42

Just don't. SQL: - does not allow for easy and clean importing of modules/libraries - is not easily to write tests for - has limited support for a debugger - lacks a consistent style for such large queries (plus most textbook cover fairly simple stuff) which means it's hard for a developer to start reading someone else's code (more than in other languages) - clearly indicates in its name that it is a Query language.…

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

Re: Stochastic gradient descent written in SQL

#106

Post by alphanumeric0 »

chrstr wrote: post #100
alphanumeric0 wrote: post #92

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?

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';

Nice, that is what I was looking for. Of course, it'd need to point to production data as well, so maybe test_case is null, in that case:

  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 TABLE data_prod AS (SELECT NULL AS test_case, prod_table.value FROM prod_table);

  CREATE VIEW data AS (SELECT * FROM data_a UNION ALL SELECT * FROM data_b UNION ALL SELECT * FROM data_prod);

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

  -- when testing
  SELECT * FROM complicated_query WHERE test_case = 'a';
  SELECT * FROM complicated_query WHERE test_case = 'b';

  -- when in 'production'
  SELECT * FROM complicated_query WHERE test_case IS NULL;

Re: Stochastic gradient descent written in SQL

#107

Post by airstrike »

Lemaxoxo wrote: post #91
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.

Then just call it Non-stochastic Gradient Descent? You can't editorialize titles per HN guidelines

https://news.ycombinator.com/newsguidelines.html

Re: Stochastic gradient descent written in SQL

#108

Post by [deleted] »

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…

[deleted]

Re: Stochastic gradient descent written in SQL

#109

Post by airstrike »

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-looking EPS estimates are generally always available. This would limit the exercise to more mature companies than the ones commonly discussed on HN but would make it actually meaningful

Re: Stochastic gradient descent written in SQL

#110

Post by malnourish »

Serow225 wrote: post #48
justsomeuser wrote: post #35

Earlier quoted context omitted.

I think general programming languages are better for general programs than SQL. Specifically they have: Type systems, compilers, debuggers, text editors, package managers, C FFI etc. But I agree that having the data and the program in the same process has benefits. Writing programs in SQL is one way. Another way is to move your data to your general program with SQLite. I like using SQL for ACID, and queries as a firs…

Another is MS SQL Server, which lets you run .NET on the database server :D "you can author stored procedures, triggers, user-defined functions, user-defined types, and user-defined aggregates in managed code"

I have had nothing but bad experiences trying to run .NET in SSIS packages -- is there another way?
Post reply on HN