30 ms·
Stochastic gradient descent written in SQL
- college_physics 4y agoIs there a risk that postgres becomes sentient in our lifetime?
- freilanzer 4y agoOr use MindsDB: https://mindsdb.com/ https://mindsdb.com/
- gigatexal 4y agoI thought I was decent at SQL until I saw this. Nice article!
- andrenotgiant 4y agoThis is really interesting, but a basic part I don't understand: What would it actually look like to run this on a live dataset? If I understand correctly: you'd run the recursive query, it produces results for every step, effectively showing you the progression of output over time, and then once it hits "present day", it completes and stops? How would you generate results going forward? I.E. A minute elapses after the results return, do you have to re-run the whole query for all time?
- Lemaxoxo 4y agoGood question. I touched upon this in the conclusion. Basically, if you run this in a streaming SQL database, such as Materialize, then you would get a true online system which doesn't restart from scratch.
- sergius 4y agoDid you try running it using DuckDB?
- Lemaxoxo 4y agoDuckDB is what I used in the blog post. Re-running this query simply recomputes everything from the start. I didn't store intermediary that would allow starting off from where the query stopped. But it's possible!
- hobs 4y agoIn a non-streaming db What would prevent you from storing the result set and just using the last iteration to calculate the next?
- Lemaxoxo 4y agoThis is definitely a possibility. What I meant to say is that the implementation in the blog post doesn't support that.
- hobs 4y agoMakes sense, just an insert instead of a select, I used a similar approach on newton-raphson to implement XIRR in SQL and it worked well.
- swasheck 4y agoi’m trying to learn here so please pardon my ignorance. wouldn’t a pre-aggregated set affect the new aggregate result? i suppose you could store avg, sum, count and then add the new value(s) to sum, new count to count, and recalculate average from that. or even just avg and count and then re-aggregate as ((avg*count)+new values)/(count + new values count) but i didn’t know if there’s a better way to process new values into a set of data that’s already been aggregated
- hobs 4y agoYep that would be what I would do - effectively a functional approach where no memory is required besides the current set and iteration. A big part of materializing datasets for performance is finding the right grain that is both easy enough to calculate and also can do nice things like be resumeable for snapshots.
- noloblo 4y agoDoes this work with Postgres?
- Lemaxoxo 4y agoPostgres has excellent support for WITH RECURSIVE, so I see no reason why it wouldn't. However, as I answered elsewhere, you would need to set some stateful stuff up if you don't want the query to start from scratch when you re-run it.
- gbrits 4y agoI for one would welcome a second blogpost with the stateful example. I’ve been doing running totals (basically balances based on adding financial tx per account) but had to do it in some client code because I couldn’t figure out how to do a stateful resume in Sql
- deleted 4y ago[deleted]
- hubja 4y agoSuper intresting! Love the scrappy mindset ;)
- Lemaxoxo 4y agoCheers, I learnt with the best :)
- snookerdooker 4y agoI challenge you to add more emojis to your bio ;)
- Lemaxoxo 4y agoYes I know some people look down on that. I hope it doesn't take away the merits of the article for you hehe.
- AndrewKemendo 4y agoThis is genius I love it. On stream inference with something like "continuous" data in the same structure I think is the final material form for "AI" so this is a great step towards that Thanks for the writeup
- jackschultz 4y ago>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 powerful than we think, they're not going to go away, only get better, and having the data and the logic in the same space really removes tons of headaches. ML models, transformation of data, generating json for an API can all be done within the database rather than outside scripting language. Are others seeing this? Are the current tradeoffs just that more people know python vs sql or database specific languages to where moving logic to postgres or snowflake is looked down on?
- actionfromafar 4y agoI have yet to see a decent IDE or system which allows great version control, unit testing and collaboration with SQL source code. So I think a lot of the reluctance is from practical concerns.
- noloblo 4y agoWe store individual sql files in github and keep them in separate folders This is very simple and scales well for our purposes
- tomrod 4y agoAbsolutely. Consider too that PostgreSQL databases support different languages, like Python. Loads of for-profit companies have tried to cash in on this. SAP HANA is one of the ones I've had recent experience with. It is unfortunately a poor implementation. The right architecture tends to be: put your model behind an API interface, not internal on the system. Train your model separately from production systems, and so on. You might also be interested in checking out MLOps platforms like Kubeflow, Flyte, and others.
- swyx 4y agohad a very good chat with https://postgresml.org/ https://postgresml.org/ last week which is focusing on bringing ML to postgres: https://youtu.be/j8hE8-jZJGU https://youtu.be/j8hE8-jZJGU
- pradeepchhetri 4y agoYou might want to consider checking out ClickHouse which supports many ML functions natively: - stochasticLinearRegression (https://clickhouse.com/docs/en/sql-reference/aggregate-functions/reference/stochasticlinearregression/ https://clickhouse.com/docs/en/sql-reference/aggregate-funct...) - stochasticLogisticRegression (https://clickhouse.com/docs/en/sql-reference/aggregate-functions/reference/stochasticlogisticregression/#agg_functions-stochasticlogisticregression https://clickhouse.com/docs/en/sql-reference/aggregate-funct...)
- Lemaxoxo 4y agoThanks for the links, I wasn't aware of them. The Russians often seem to have a step ahead in the ML world.
- UnCommonLisp 4y agoThat is an outdated view, it is a global and culturally diverse company.
- glogla 4y agoI always love DuckDB articles because I can learn what is new in ClickHouse.
- yttribium 4y agoAt least one F500 company had iteratively reweighted least squares implemented in MS SQL as of ~20y ago. When all you have is a hammer...
- saydaark 4y agoThis is interesting, but is not using the GPU.
- yza 4y agoNext step would be to implement autodiff. If only PostgreSQL had CUDA support.
- Rickasaurus 4y agoPlease don't do this in prod
- dventimi 4y agoMany ML models experience offline training, and some online training is fine-tuning a model that was pretrained offline. Hence, I see no particular reason to worry about doing this "in prod."
- syats 4y agoJust 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. Save yourself the trouble and all your collaborators the pain of working with this code in the future, of trying to add new features, of trying to reuse it in another project. If you want to operate near the data, use PL/Python for PostgreSQL. EDIT: Fixed formatting.
- dventimi 4y ago-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?
- syats 4y agoRe 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 of the extension, which makes compounding them a bit hard. Not impossible, just hard to maintain, About your last point, I don't think that was my line of reasoning, but, yes, for the love of what is precious, don't open SQL files as python/java file objects and then parse and rummage through them to find the data you are looking for. Not impossible, just hard to maintain. Thanks for pointing out pgTAP, didn't know about this. For some reason, data-science folks haven't yet caught up with ORMs.. I don't know if this is good or bad, but (as the OP shows) they are more used to rows and columns (or graphs) than objects. Maybe that will change one day.
- kilgnad 4y agoAn even MORE useful idea is to do this in CSS.
- episode0x01 4y agoFrom a comment by the author: > Also, I can't justify why, but my gut feeling is that the database should be an order of magnitude faster than Python, provided the implementation were to be improved. Would be curious how that could end up being the case. Perhaps if NumPy wasn't used at all? That would mean no vectorization or optimized storage. Would be interesting to see how it scaled with length and dimensionality
- krick 4y agoFrom 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?
- guhidalg 4y agoWhy 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?
- sgu999 4y agoI'm very confused as well. Are we facing a wall of GPT-generated comments?
- rlewkov 4y agoUhhhh, ya know that Python has a library for that :-)
- Foobar8568 4y agoR has been natively supported in sql server for a long while (At least since 2017), same for Java or python, e.g. Writing SP in these languages.
- simonw 4y agoI tried to replicate this in SQLite. The first few steps worked OK, e.g. https://lite.datasette.io/?json=https://gist.github.com/simonw/f750e08b33851a5ac8786d0b717a0152#/data?sql=WITH+RECURSIVE%0A++++stream+AS+%28%0A++++++++SELECT+ROW_NUMBER%28%29+OVER+%28%29+AS+step%2C+%22Adj+Close%22+AS+x%0A++++++++FROM+raw%0A++++++++ORDER+BY+step%0A++++%29%2C%0A++++state%28step%2C+x%2C+avg%29+AS+%28%0A++++++++--+Initialize%0A++++++++SELECT+step%2C+x%2C+x+AS+avg%0A++++++++FROM+stream%0A++++++++WHERE+step+%3D+1%0A++++++++UNION+ALL%0A++++++++--+Update%0A++++++++SELECT%0A++++++++++++stream.step%2C%0A++++++++++++stream.x%2C%0A++++++++++++state.avg+%2B+%28stream.x+-+state.avg%29+%2F+stream.step+AS+avg%0A++++++++FROM+stream%0A++++++++INNER+JOIN+state+ON+state.step+%2B+1+%3D+stream.step%0A++++%29%0A%0ASELECT+*%0AFROM+state%0AORDER+BY+step+DESC%0ALIMIT+5 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/?json=https://gist.github.com/simonw/f750e08b33851a5ac8786d0b717a0152#/data?sql=WITH+RECURSIVE%0A++++stream+AS+%28%0A++++++++SELECT%0A++++++++++++ROW_NUMBER%28%29+OVER+%28%29+AS+step%2C%0A++++++++++++%22Adj+Close%22+AS+x%2C%0A++++++++++++%22Close%22+AS+y%0A++++++++FROM+raw%0A++++%29%2C%0A++++state%28step%2C+x%2C+x_avg%2C+y%2C+y_avg%2C+cov%29+AS+%28%0A++++++++--+Initialize%0A++++++++SELECT%0A++++++++++++step%2C%0A++++++++++++x%2C%0A++++++++++++x+AS+x_avg%2C%0A++++++++++++y%2C%0A++++++++++++y+AS+y_avg%2C%0A++++++++++++0.0+AS+cov%0A++++++++FROM+stream%0A++++++++WHERE+step+%3D+1%0A++++++++UNION+ALL%0A++++++++--+Update%0A++++++++SELECT%0A++++++++++++step%2C%0A++++++++++++x%2C%0A++++++++++++x_new_avg+AS+x_avg%2C%0A++++++++++++y%2C%0A++++++++++++y_new_avg+AS+y_avg%2C%0A++++++++++++cov+%2B+%28%28x+-+x_prev_avg%29+*+%28y+-+y_new_avg%29+-+cov%29+%2F+step+AS+cov%0A++++++++FROM+%28%0A++++++++++++SELECT%0A++++++++++++++++stream.step%2C%0A++++++++++++++++stream.x%2C%0A++++++++++++++++stream.y%2C%0A++++++++++++++++state.x_avg+AS+x_prev_avg%2C%0A++++++++++++++++state.x_avg+%2B+%28stream.x+-+state.x_avg%29+%2F+stream.step+AS+x_new_avg%2C%0A++++++++++++++++state.y_avg+AS+y_prev_avg%2C%0A++++++++++++++++state.y_avg+%2B+%28stream.y+-+state.y_avg%29+%2F+stream.step+AS+y_new_avg%2C%0A++++++++++++++++state.cov%0A++++++++++++FROM+stream%0A++++++++++++INNER+JOIN+state+ON+state.step+%2B+1+%3D+stream.step%0A++++++++%29%0A++++%29%0A%0ASELECT+step%2C+cov%0AFROM+state%0AORDER+BY+step+DESC%0ALIMIT+5&DOUBLE= https://lite.datasette.io/?json=https://gist.github.com/simo...
- 4y ago
- Narew 4y agoThere was also this neural network module in redis if you want to do training and inference in a redid DB. (quite old btw) https://github.com/antirez/neural-redis https://github.com/antirez/neural-redis
- zackmorris 4y agoThis is great! Moving away from the proprietary nature of GPUs and complex math gatekeeping should help democratize AI. Has anyone converted stuff like gradient descent to set theory? https://support.unicomsi.com/manuals/soliddb/7/SQL_Guide/2_GettingStartedSQL.03.04.html https://support.unicomsi.com/manuals/soliddb/7/SQL_Guide/2_G... https://www.sqlshack.com/mathematics-sql-server-fast-introduction-set-theory/ https://www.sqlshack.com/mathematics-sql-server-fast-introdu... https://www.sqlshack.com/learn-sql-set-theory/ https://www.sqlshack.com/learn-sql-set-theory/ Right now AI algorithms kind of look imperative and stateful to me, like state machines. But there should be a functional or relational representation, where pattern matching would be used to derive the current state instead. It's trivial to go from functional to imperative representation, but often nearly impossible to go the other way. That's why monadic (sorry if I'm mincing terms) logic is so troublesome. Mutable variables, futures/promises, async, etc can't be statically analyzed, which is why most code today is difficult to parallelize and stuff like C++ optimizers don't even increase speed to the next order of magnitude. But functional languages have nearly infinite optimization potential through divide and conquer approaches like sharding and scatter-gather arrays that can run internally without side effects. In other words, we can throw hardware at SQL for linear speedup since it's embarrassingly parallel, but might have limited success optimizing something like Python.
- dogcomplex 4y agoReplying to this to remind myself to research later, as this is a very important question
- hdespiritu 4y agoYour question "Has anyone converted stuff like gradient descent to set theory?" doesn't make sense from the perspective that gradient descent uses differential calculus to find min/max points of an objective function and differential calculus requires a lot of additional assumptions on top of set theory. That being said, current deep learning libraries such as JAX and Pytorch use automatic differentiation to efficiently compute partial derivatives used for optimization algorithms such as gradient descent and it's not clear to me what the level of effort would be to convert that to something that could run efficiently in SQL?
- civilized 4y agoIn the comments here so far, we see a pattern we've seen before. When someone suggests doing something in SQL, there's a lot of concern about SQL being a very limited programming language where it's hard to do proper engineering. Here's I would really love to know: why is it that SQL is, to first order, the only language used to interact with databases, and SQL has about the same features as it did in the 70s? It seems analogous to if the general-purpose programming world stopped with C.
- maxfurman 4y agoPlenty of SQL features have been added since the 70s, notably window functions (which TFA relies on heavily). Most of the major databases are from the 80s, and even new kid on the block MySQL has been around since the mid 90s. SQL draws a very hard line between the expression of the query and the AST that is used in the actual implementation. Database vendors like this aspect because they are free to implement whichever optimizations they want, but application developers want to build queries programmatically and optimize them themselves before passing them to the db engine, hence the tension in threads like these.
- bob1029 4y ago> why is it that SQL is, to first order, the only language used to interact with databases, and SQL has about the same features as it did in the 70s? Because SQL is effectively a domain-specific language. If you added 100+ additional keywords/functions/etc., do you think it would be easier or more difficult for the average developer to build something clean with it? I look at SQL like a pile of bricks. You don't want complicated, unicorn bricks or you won't be able to fit anything meaningful together. Experienced masons almost certainly prefer their materials to be as dumb and consistent as possible.
- CRConrad 4y ago> You don't want complicated, unicorn bricks or you won't be able to fit anything meaningful together. Experienced masons almost certainly prefer their materials to be as dumb and consistent as possible. And now we know what's gone wrong with Lego for the last two-three (close to four?) decades. Funny – and sad! – how popular this shit has been on sites like this, though. Dare one hope that that popularity might begin to wane? Idunno
- kunalgupta 4y agogod tier
- deleted 4y ago[deleted]
- Terretta 4y agoTitle 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 stochastic: the rows are not shuffled. ⇠ ⇠ ⇠ ⇠ - Squared loss, which is the standard loss for regression. - No gradient clipping. - No weight regularisation. - No intercept term.
- Lemaxoxo 4y agoHehe 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.
- airstrike 4y agoThen just call it Non-stochastic Gradient Descent? You can't editorialize titles per HN guidelines https://news.ycombinator.com/newsguidelines.html https://news.ycombinator.com/newsguidelines.html
- westurner 4y agoWhat are some adversarial cases for gradient descent, and/or what sort of e.g. DVC.org or W3C PROV provenance information should be tracked for a production ML workflow? Gradient descent: https://en.wikipedia.org/wiki/Gradient_descent https://en.wikipedia.org/wiki/Gradient_descent Stochastic gradient descent: https://en.wikipedia.org/wiki/Stochastic_gradient_descent https://en.wikipedia.org/wiki/Stochastic_gradient_descent Online machine learning: https://en.wikipedia.org/wiki/Online_machine_learning https://en.wikipedia.org/wiki/Online_machine_learning adversarial gradient descent site:github.com inurl:awesome : https://www.google.com/search?q=awesome+adversarial+gradient+descent+site%3Agithub.com https://www.google.com/search?q=awesome+adversarial+gradient... https://github.com/EthicalML/awesome-production-machine-learning#adversarial-robustness https://github.com/EthicalML/awesome-production-machine-lear... Robust machine learning: https://en.wikipedia.org/wiki/Robustness_(computer_science)#Robust_machine_learning https://en.wikipedia.org/wiki/Robustness_(computer_science)#... Robust gradient descent
- glomgril 4y agoIncredible post. I laughed when I saw the title, snickered at the first paragraph, and then proceeded to be blown away by the rest of it. Thought I was in for a joke and instead I'm thinking about the nature of ML Ops and what it's become.
- hdespiritu 4y agoMy sentiment exactly. The premise of the article comes across a little naive because there are so many fine-tuned libraries for specialized hardware architectures that already do this computation very efficiently. However, it did make me wonder what this might look like on a gpu-accelerated database engine that is designed to leverage the SIMD parallelism of GPGPU architectures. Beyond using SQL/NoSQL databases for CRUD apps I am not a "database guy", so I'm not sure about the feasibility, but it would be interesting to see it implemented.
- PaulHoule 4y agoReminds me of the time I coded up an optimized nearest neighbor search in SQL.
- airstrike 4y agoThis 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
- Lemaxoxo 4y agoDo you have some data/resources on this? I'm a total snowflake at this, but I'm willing to learn.
- airstrike 4y agoIn hindsight this is harder than it seems if you don't already have access to data, so I'll try to be as informative as possible in my response It's hard to find this information out there, so here's ~all you need to know. Data is usually behind paywalls, unfortunately. Industry standards are Bloomberg terminal (ridiculously expensive, 5 digits $), FactSet (very expensive, 4 digits), Capital IQ (expensive, not sure)... but there are a number of up-and-coming startups trying to disrupt the space so you may be able to grab data from them. I think https://atom.finance https://atom.finance has a 7-day free trial you could use to play around with. P/E simply means the company's _P_rice per share divided by _E_arnings per share. Cancel out the "per share" terms and you get total market capitalization (which is the value of the total equity) divided by net income (since "earnings per share" really means "net income per share") So the "P" is easy to get. It's your Adj Close. The "E" is trickier as it can mean a lot of things. Diluted EPS from financial statements? Last year's EPS? Management's guidance for EPS? None of those are actually correct even if they are all "EPS" Importantly--and contrary to 99% of the info you will find online--the most relevant EPS number are forward estimates of EPS, usually for the next twelve months ("NTM"). That is based on an average or median of analyst estimates which is called "consensus". These are analysts from financial institutions who build their own little models based on their own views of where the business is going to go, informed by recent earnings, management's color in earnings calls and filings, etc. Believe it or not, as hairy as that sounds, EPS is fairly easy to get as it's a metric that has less room for interpretation than, say, EBITDA. So you're not going to go out there, read all these (paid) analyst reports, find their EPS, calculate the median, etc. Bloomberg, Capital IQ, FactSet do this for you and it's easily observable for the end user (that's their business). The thing is, as you may have guessed, "next twelve months" are a moving target across time. Analysts usually provide estimates for the current fiscal year (i.e. FY 2023, ending 12/31/2023 for most companies) and the following year, ending 12/31/2024. Let's call these FY0_EPS and FY1_EPS, for simplicity You might be tempted to just take a moving average of these two estimates, so that on 1/1/2023 it is 100% of FY0_EPS + 0% of FY1_EPS, on 1/2/2023 it is 99.9% + 0.1% and gradually "move forward in time" as the days pass. That sort of works (and definitely checks the box for a proof-of-concept like in your post) but for the sake of completeness, I'll just say that the right-er approach is to only "move forward in time" when new earnings are released. So it doesn't matter if we're in 1/1/2023 or 2/1/2023--what matter is what is the latest reported quarter. Take Coca-Cola for instance (https://www.bamsec.com/companies/21344/coca-cola-co https://www.bamsec.com/companies/21344/coca-cola-co). Let's roll the tape backward one year. They reported FY 2021 earnings on 2/22/2022, at which point analysts published new estimates in revised models, so on from that day forward until the next quarterly earnings we take 100% FY0_EPS + 0% FY1_EPS, in which these correspond to estimates for FY 2022 and FY 2023, respectively. On 4/1/2022, Coca-Cola reported Q1 2022 results, analysts published new estimates, and we now take 75% FY0_EPS + 25% FY1_EPS. On 7/1/2022, we move forward another quarter so 50% + 50%, then 25% + 75% starting on 10/26 and then back to square one with 100% + 0% except FY0_EPS now means FY 2023 vs FY 2022 previously, and FY1_EPS means FY 2024 So your table is something like (I'm making up numbers) +------------+--------+-----------+---------+---------+-------------+-------------+-------------+ | Date | Ticker | Adj_Close | FY0_EPS | FY1_EPS | Period | NTM0_Weight | NTM1_Weight | | 01/01/2022 | KO | 90.10 | 20.00 | 24.00 | Q1 2022 | 1.00 | 0.00 | | 01/02/2022 | KO | 91.14 | 20.00 | 24.00 | Q1 2022 | 1.00 | 0.00 | | 01/03/2022 | KO | 89.30 | 20.00 | 24.00 | Q1 2022 | 1.00 | 0.00 | | 01/04/2022 | KO | 91.09 | 20.00 | 24.00 | Q1 2022 | 1.00 | 0.00 | | 01/05/2022 | KO | 92.01 | 20.00 | 24.00 | Q1 2022 | 1.00 | 0.00 | | 01/06/2022 | KO | 89.05 | 20.00 | 24.00 | Q1 2022 | 1.00 | 0.00 | ... | 07/02/2022 | KO | 89.05 | 19.50 | 23.20 | Q2 2022 | 0.75 | 0.25 | With that you can take NTM0_Weight and NTM1_Weight to calculate NTM_EPS by multiplying those weights by FY0_EPS and FY1_EPS. And then can take AdjClose / NTM_EPS to calculate P/E Why is this useful? Because in theory you can take the average P/E of companies X, Y and Z in one industry and compare it to a fourth company W. Is W's P/E multiple above or below the industry average? You now know if they are over or undervalued, respectively, which means you know if you should buy or sell that stock (if you believe you picked the right "comparable" companies in that industry) This is just one example... there are all sorts of similar analyses done daily in the financial services industry. I'm not saying it's easy to extract alpha from trading on these, but that's the framework
- agnosticmantis 4y agoJust to disambiguate, here machine learning = linear regression. So no auto-grad needed as gradients are explicit and simple. Still interesting though.
- victorNicollet 4y agoWe've been doing this type of gradient descent in production for a couple of years now, and we've been very happy with it. A few insights from my experience: One of the main benefits of doing differentiable programming over a relational database is that you can use joins to traverse the normalized data schema, and this acts as a very effective prior for your model. For example, you can learn parameters associated to shirt colors by having a parameter in the color table, and joining it with the shirt sales table (through the shirts table). And knowledge of the table structure also lets you improve convergence, for example by adding a factor to compensate for the fact that not all colors have the same number of sales (and therefore have been updated fewer times during each epoch). Here's a paper from last year: https://arxiv.org/abs/2209.03771v1 https://arxiv.org/abs/2209.03771v1 Automatic differentiation is an absolute must-have. Once you start having a few complex joins and aggregations, it's too hard to derive the gradient by hand. Doing automatic differentiation on relational queries requires some adjustments (in particular, because you cannot use a tape), so you end up having to define a subset of relational algebra that is closed by automatic differentiation. The general ideas were presented in https://ceur-ws.org/Vol-2971/paper07.pdf https://ceur-ws.org/Vol-2971/paper07.pdf On the other hand, while it's possible to do SQL-to-SQL automatic differentiation, the resulting queries have poor performance, so it's better to go one step lower (at the level of the executor for an already-planned query) and perform the automatic differentiation there. And we had an excellent intern work with us on dedicated parallelization for gradient descent queries: https://blog.lokad.com/pdf/reproducible-parallel-sgd-ziyad-benomar-2022-09.pdf https://blog.lokad.com/pdf/reproducible-parallel-sgd-ziyad-b... Finally, in practice, it usually doesn't matter if the gradient descent is stochastic, so long as you run several passes over the data. I'm very interested in differentiable programming on relational languages, as the mainstream research concentrates on unstructured, low-information-density data (images/sound/text) when there are many domains where the data is more dense and structured, and (because of that structure) is stored in a database.