6 ms·

SQL is a mind bender for me. I do a lot of work on Data, use python and pandas to do a lot of data magic, but the problem with me is my mind is too procedural i

SQL is a mind bender for me. I do a lot of work on Data, use python and pandas to do a lot of data magic, but the problem with me is my mind is too procedural in thinking. - Step 1 - Step 2 - Loop through results in Step 2 - Curate and finish output. I try very hard to transform the above steps into an SQL statement spanning multiple tables, but always fail and I usually fallback to python for manually extracting and processing the data. Does anyone else face this problem? Any suggested guides / books to make me think more SQL'ley ?

ramraj07 8y ago
I've thought about the same problem and think I have an explanation. SQL inherently avoids operations that cannot scale. This means that while the same operation can be written much more simpler as code if you're doing with pandas or spark, trying to do it with SQL would be less forgiving if your underlying data schema is not optimal or if your logic isn't scalable. But more often than not, the SQL query will run faster and more reliably given similar amounts of compute power to the two engines. Of course you can always screw up and write suboptimal SQL that will take forever to finish, but it's just harder to do unless you're really trying to be dumb about it. So I do think there's still a lot of merit in trying to learn and use SQL more.
- sixo 8y ago
  It also ignores operations of type (set of rows) => (set of rows) or (row) => (set of rows). and makes you jump through hoops of CTEs to implement them except in some special cases. Limited versions of these (flatMaps) work just fine in spark and have no problems scaling.
- kumarvvr 8y ago
  I want to learn and do the same things in SQL because, I don't always have the opportunity to run python programs on the database server environment. There are certain situations where an SQL query is all that I can work with.
fdsak 8y ago
A good starting point could be understanding https://en.wikipedia.org/wiki/Declarative_programming https://en.wikipedia.org/wiki/Declarative_programming
bluetwo 8y ago
I like the Ben Forta SQL in Minutes book. Just the basics, very clear and easy to understand.
- bas 8y ago
  Seconded.
beatgammit 8y ago
I took a course in prolog, really seemed to help get my mind in the right place. Basically, instead of thinking in terms of how to get to the result, think instead of the result and figure out how to get there. For example, let's say I need to get a mapping of students to their classes. One way would be to get the students, then loop through that to get classes for each one. Another way to do it would be to ask the database to combine those sets of data for us instead and return the result (join students and class tables on the student ID). Basically, find the data you want from each table, tell the database about the relationship between them (join on <field>), and set up your constraints. I guess you could think about the query as a function, and you're just writing the unit tests for it (when I call X with parameters Y, I expect Z).
- arkh 8y ago
  > I took a course in prolog, really seemed to help get my mind in the right place. I have the reverse problem: every time I motivate myself to learn Prolog I feel like I could just input the constraints in a db and use SQL to get my results.
conception 8y ago
I think what your are doing is fine. SQL is strongest at answering questions not processing data. I think metabase has the right approach: What question do you want to ask your data? If you want to process and transform your data, I think your tools you are using are great for that.
- eleclady 8y ago
  Not trying to play word games, but what is the difference between answering a question and processing data? Aren't they effectively the same? Using another tool for processing data often results in recreating SQL mechanics at application level. E.g. select this data, retrieve it, loop and if this, then set that, etc. SQL does it way better, guaranteed. Of course that's often required for technical reasons (scalability etc.) or processing that's too complex to implement at data layer, or just for cleaner design. But SQL is amazing at processing data!
  - veritas3241 8y ago
    Agreed that SQL is amazing at processing data! I would argue that a lot of people are trying to both process their data _and_ ask a question of it in the same statement. Separating those out is really important to make analytics more scalable. We do >95% of our transformations with pure SQL and the queries are primarily in Looker.
  - CRConrad 8y ago
    «what is the difference between answering a question and processing data? Aren't they effectively the same?» I think it influences the mindset of the developer. As you say, “retrieve ... if this, then ... loop”. If you're in a “data processing” mindset, then you'll think of a problem like “Get the total number of car widgets in the warehouse” as fetch a widget row; if it's of type car, add number to total; loop until you've processed every row; there you have your total. If, OTOH, you're in an “asking questions” mindset, you'll go: What was the question again, exactly? Oh yes, get the sum of the number for all the widgets which are of type car widgets. Which is almost exactly the same as SELECT SUM(NUMBER) FROM WIDGET WHERE TYPE = 'CAR';. Processing data is when you do it (in code); answering questions is when the RDBMS (i.e, its code) does it for you. :-) (At least that's what I think the difference is _in terms of vvkumar's original question._)
kccqzy 8y ago
Try some functional programming. It will liberate your brain from thinking operationally. You will soon no longer think in terms of steps, loops or anything like that. You will get used to thinking about data transformations, which is what SQL is good at.
- sk5t 8y ago
  I was about to add exactly this. Setwise operations, map/transform, aggregate, avoidance of mutable state, filters, avoidance of if/for with break conditions, quite strong avoidance of exceptions.
- kumarvvr 8y ago
  Any books that teach function programming with python?
  - tartoran 8y ago
    https://docs.python.org/2/howto/functional.html https://docs.python.org/2/howto/functional.html
  - Zizizizz 8y ago
    https://docs.python.org/3.7/howto/functional.html https://docs.python.org/3.7/howto/functional.html
mutagen 8y ago
I have the same issue, procedural thinking. I've made some small progress thinking in sets and working to re-phrase my goals in terms of sets of data, sometimes using window functions (row_number(), etc) when I need n-1 items from the query. Some problems can to be assembled from CTEs / subqueries when you might have reached for a procedural solution to 'finish' the problem. Grasping CTEs was a big thing for me and now I'm wrestling with issues of style mixing CTEs with tiny subqueries and wondering if the increase in readability (for me) is correct. I'd love to see suggestions for digging deeper on this too.
eleclady 8y ago
I used to think this too, and wondered what magical SQL perspective my brain was missing. I'd see these elegant but seemingly impenetrable queries and wonder why my brain wasn't in the "SQL dimension" yet. But over time, and very heavy SQL reporting, I realized the procedural mindset still applies, it's just not expressed as such. You need to think through the steps of how you want to retrieve, transform, aggregate etc. the data and build up the query to accomplish those steps. The resulting query doesn't look procedural, but the thought process was. Of course you need to know the sometimes opaque constructs/tricks/syntax to express the steps, which again, look like magic at face value or just reading docs. I think this is why people struggle with understanding SQL. The thought process is still procedural, but the query isn't. You need to translate the query back into procedural terms for your own understanding, which is a PITA.
- kumarvvr 8y ago
  To me trying to emulate procedural stuff in SQL feels wrong, especially with multi-table queries.
  - eleclady 8y ago
    Yes, that's what I'm saying. The difficulty people have is that the thought process (solution) is still procedural but the query is expressed declaratively. Once you learn how to navigate that gap you get good at SQL.
ghufran_syed 8y ago
I really like "Beginning database design" by Claire Churcher, and the follow-on book "Beginning SQL queries" . They sound like really basic books, and they definitelystart from the basics, but then build up steadily.
bigger_cheese 8y ago
I work a lot with data as well primarily I use SAS, R and SQL. I'm also very procedurally oriented (as in I prefer breaking task down into discrete chunks), R's functional way of chaining operations with pipe operator feels so alien to me. I find myself having the opposite problem I probably use (abuse?) SQL more than should. My world (industrial plant) is very DB/historian heavy everything speaks SQL it's pretty much the common tongue connecting everything. I think this is slowly changing some PLC/historians now offer a webservice which returns JSON objects via an ajax query - personally I vastly prefer SQL to ajax. I'd estimate for a typical problem I'm working with maybe 90% is done in SQL vs 10% in R/SAS code. When I need Regression, Principal Component Analysis, Time Series manipulation, Plots etc I have to break into dedicated language. For most other things - extract, merge, filtering, high level aggregation (Count sum etc) type of operations using SQL feels more natural and expressive to me.
dwd 8y ago
If raw speed is not an issue you can always fall back on temp tables or a cursor for those cases where you might have extracted an initial dataset to then iterate over. EDIT: A CTE (Common Table Expression) might be what you're thinking you need. You can do some fun recursive queries with them.
- kumarvvr 8y ago
  > recursive queries It's like all my fears concentrated, like the power of a thousand suns, onto a single sentence.
  - dwd 8y ago
    I should have mentioned cursors are conceptually a C pointer to really put the fear into people.
esfandia 8y ago
If you're mathematically minded (really just basic set theory), maybe try learning relational calculus. You'll express database queries in terms of Cartesian products, selections and projections. These operations translate directly into SQL, so maybe it'll help you think clearly first, then apply the syntactic translation. Honestly, if I had the choice I'd rather just use the syntax of relational calculus, as I find SQL too verbose and convoluted.
daveslash 8y ago
Two Things. (1) Think of a database as a "big pile of stuff in a room". Some of it's ordered in a sane way, some of it's not. There is "a person at the door" to the room preventing you from entering - this is the Database Engine, not to be confused with the Database itself. You need to get things out of the room, and you need to put things in the room. You're not allowed to enter. If you want something out of the room, you must ask for it (query) by specifying what the thing looks like and which part of the pile you think it's in. If you want to put something in the room, you must provide explicit instructions for where it must go. When you write SQL, you are either asking a question of or giving instructions to the database engine. The DB Engine is distinct from the Database (the big pile of stuff...which you never have direct access to). (2) As others have pointed out, SQL is very much the mathematics of Set Theory put into a programming language. Stanford has a free class on Databases. I took it a few years ago (it may have changed), and most of it had no SQL at all. It was fairly very straight forward - things you can do with pencil and paper. It's free, so no pressure. Go sign up and do some of the homework assignments. Do them until you get them right (you can re-take wrongly answered problems immediately). It'll help break your procedural mindset. https://online.stanford.edu/courses/soe-ydatabases-databases https://online.stanford.edu/courses/soe-ydatabases-databases
- kumarvvr 8y ago
  Thank you for the Stanford class reference. Will check it out.
  - camel_Snake 8y ago
    I took this one ages ago, before I started programming (in prep for a bootcamp) and really enjoyed it.
thomassantosh 8y ago
Highly recommend Dmitri Fontaine's book "Mastering PostgreSQL in Application Development". Really helps emphasize how you can leverage the power of SQL to minimize all the Python / Panda steps to get the same result.
ddebernardy 8y ago
Reading the Postgres manual cover to cover is a fairly good starting point.
rsyring 8y ago
These mental paradigms helped me a great deal: 1) Think in terms of excel spreadsheets. At each step in your SQL you are creating a set of data. Doesn't usually matter how many rites it is, you just want the right columns. 2) Once you have a recordset, decide how the next recordset goes together. I would mentally visualize putting one worksheet next to the other and think through what columns I had to join together to get the rows I wanted. 3) Make sure you know what your first recordset should be. This is the base data that everything can be joined into. I refer to this as the domain of records.
bashmydotfiles 8y ago
I still struggle with thinking too "proceduraly" sometimes, but what really helps out with me is drawing / envisioning the sets of data as a series of circles and Venn diagrams. It also helps to change the language you use in your inner monologue. Instead of thinking, "For each row in table A...", you should think, "For all the rows in table A that match on...".
zeveshe 8y ago
This book can help with getting into the right mindset: https://www.amazon.com/Joe-Celkos-Thinking-Sets-Management/dp/0123741378 https://www.amazon.com/Joe-Celkos-Thinking-Sets-Management/d...

ramraj07 8y ago

I've thought about the same problem and think I have an explanation. SQL inherently avoids operations that cannot scale. This means that while the same operation can be written much more simpler as code if you're doing with pandas or spark, trying to do it with SQL would be less forgiving if your underlying data schema is not optimal or if your logic isn't scalable. But more often than not, the SQL query will run faster and more reliably given similar amounts of compute power to the two engines. Of course you can always screw up and write suboptimal SQL that will take forever to finish, but it's just harder to do unless you're really trying to be dumb about it. So I do think there's still a lot of merit in trying to learn and use SQL more.

sixo 8y ago
It also ignores operations of type (set of rows) => (set of rows) or (row) => (set of rows). and makes you jump through hoops of CTEs to implement them except in some special cases. Limited versions of these (flatMaps) work just fine in spark and have no problems scaling.
kumarvvr 8y ago
I want to learn and do the same things in SQL because, I don't always have the opportunity to run python programs on the database server environment. There are certain situations where an SQL query is all that I can work with.

fdsak 8y ago

A good starting point could be understanding https://en.wikipedia.org/wiki/Declarative_programming https://en.wikipedia.org/wiki/Declarative_programming

bluetwo 8y ago

I like the Ben Forta SQL in Minutes book. Just the basics, very clear and easy to understand.

bas 8y ago
Seconded.

beatgammit 8y ago

I took a course in prolog, really seemed to help get my mind in the right place. Basically, instead of thinking in terms of how to get to the result, think instead of the result and figure out how to get there. For example, let's say I need to get a mapping of students to their classes. One way would be to get the students, then loop through that to get classes for each one. Another way to do it would be to ask the database to combine those sets of data for us instead and return the result (join students and class tables on the student ID). Basically, find the data you want from each table, tell the database about the relationship between them (join on <field>), and set up your constraints. I guess you could think about the query as a function, and you're just writing the unit tests for it (when I call X with parameters Y, I expect Z).

arkh 8y ago
> I took a course in prolog, really seemed to help get my mind in the right place. I have the reverse problem: every time I motivate myself to learn Prolog I feel like I could just input the constraints in a db and use SQL to get my results.

conception 8y ago

I think what your are doing is fine. SQL is strongest at answering questions not processing data. I think metabase has the right approach: What question do you want to ask your data? If you want to process and transform your data, I think your tools you are using are great for that.

eleclady 8y ago
Not trying to play word games, but what is the difference between answering a question and processing data? Aren't they effectively the same? Using another tool for processing data often results in recreating SQL mechanics at application level. E.g. select this data, retrieve it, loop and if this, then set that, etc. SQL does it way better, guaranteed. Of course that's often required for technical reasons (scalability etc.) or processing that's too complex to implement at data layer, or just for cleaner design. But SQL is amazing at processing data!
- veritas3241 8y ago
  Agreed that SQL is amazing at processing data! I would argue that a lot of people are trying to both process their data _and_ ask a question of it in the same statement. Separating those out is really important to make analytics more scalable. We do >95% of our transformations with pure SQL and the queries are primarily in Looker.
- CRConrad 8y ago
  «what is the difference between answering a question and processing data? Aren't they effectively the same?» I think it influences the mindset of the developer. As you say, “retrieve ... if this, then ... loop”. If you're in a “data processing” mindset, then you'll think of a problem like “Get the total number of car widgets in the warehouse” as fetch a widget row; if it's of type car, add number to total; loop until you've processed every row; there you have your total. If, OTOH, you're in an “asking questions” mindset, you'll go: What was the question again, exactly? Oh yes, get the sum of the number for all the widgets which are of type car widgets. Which is almost exactly the same as SELECT SUM(NUMBER) FROM WIDGET WHERE TYPE = 'CAR';. Processing data is when you do it (in code); answering questions is when the RDBMS (i.e, its code) does it for you. :-) (At least that's what I think the difference is _in terms of vvkumar's original question._)

kccqzy 8y ago

Try some functional programming. It will liberate your brain from thinking operationally. You will soon no longer think in terms of steps, loops or anything like that. You will get used to thinking about data transformations, which is what SQL is good at.

sk5t 8y ago
I was about to add exactly this. Setwise operations, map/transform, aggregate, avoidance of mutable state, filters, avoidance of if/for with break conditions, quite strong avoidance of exceptions.
kumarvvr 8y ago
Any books that teach function programming with python?
- tartoran 8y ago
  https://docs.python.org/2/howto/functional.html https://docs.python.org/2/howto/functional.html
- Zizizizz 8y ago
  https://docs.python.org/3.7/howto/functional.html https://docs.python.org/3.7/howto/functional.html

mutagen 8y ago

I have the same issue, procedural thinking. I've made some small progress thinking in sets and working to re-phrase my goals in terms of sets of data, sometimes using window functions (row_number(), etc) when I need n-1 items from the query. Some problems can to be assembled from CTEs / subqueries when you might have reached for a procedural solution to 'finish' the problem. Grasping CTEs was a big thing for me and now I'm wrestling with issues of style mixing CTEs with tiny subqueries and wondering if the increase in readability (for me) is correct. I'd love to see suggestions for digging deeper on this too.

eleclady 8y ago

I used to think this too, and wondered what magical SQL perspective my brain was missing. I'd see these elegant but seemingly impenetrable queries and wonder why my brain wasn't in the "SQL dimension" yet. But over time, and very heavy SQL reporting, I realized the procedural mindset still applies, it's just not expressed as such. You need to think through the steps of how you want to retrieve, transform, aggregate etc. the data and build up the query to accomplish those steps. The resulting query doesn't look procedural, but the thought process was. Of course you need to know the sometimes opaque constructs/tricks/syntax to express the steps, which again, look like magic at face value or just reading docs. I think this is why people struggle with understanding SQL. The thought process is still procedural, but the query isn't. You need to translate the query back into procedural terms for your own understanding, which is a PITA.

kumarvvr 8y ago
To me trying to emulate procedural stuff in SQL feels wrong, especially with multi-table queries.
- eleclady 8y ago
  Yes, that's what I'm saying. The difficulty people have is that the thought process (solution) is still procedural but the query is expressed declaratively. Once you learn how to navigate that gap you get good at SQL.

ghufran_syed 8y ago

I really like "Beginning database design" by Claire Churcher, and the follow-on book "Beginning SQL queries" . They sound like really basic books, and they definitelystart from the basics, but then build up steadily.

bigger_cheese 8y ago

I work a lot with data as well primarily I use SAS, R and SQL. I'm also very procedurally oriented (as in I prefer breaking task down into discrete chunks), R's functional way of chaining operations with pipe operator feels so alien to me. I find myself having the opposite problem I probably use (abuse?) SQL more than should. My world (industrial plant) is very DB/historian heavy everything speaks SQL it's pretty much the common tongue connecting everything. I think this is slowly changing some PLC/historians now offer a webservice which returns JSON objects via an ajax query - personally I vastly prefer SQL to ajax. I'd estimate for a typical problem I'm working with maybe 90% is done in SQL vs 10% in R/SAS code. When I need Regression, Principal Component Analysis, Time Series manipulation, Plots etc I have to break into dedicated language. For most other things - extract, merge, filtering, high level aggregation (Count sum etc) type of operations using SQL feels more natural and expressive to me.

dwd 8y ago

If raw speed is not an issue you can always fall back on temp tables or a cursor for those cases where you might have extracted an initial dataset to then iterate over. EDIT: A CTE (Common Table Expression) might be what you're thinking you need. You can do some fun recursive queries with them.

kumarvvr 8y ago
> recursive queries It's like all my fears concentrated, like the power of a thousand suns, onto a single sentence.
- dwd 8y ago
  I should have mentioned cursors are conceptually a C pointer to really put the fear into people.

esfandia 8y ago

If you're mathematically minded (really just basic set theory), maybe try learning relational calculus. You'll express database queries in terms of Cartesian products, selections and projections. These operations translate directly into SQL, so maybe it'll help you think clearly first, then apply the syntactic translation. Honestly, if I had the choice I'd rather just use the syntax of relational calculus, as I find SQL too verbose and convoluted.

daveslash 8y ago

Two Things. (1) Think of a database as a "big pile of stuff in a room". Some of it's ordered in a sane way, some of it's not. There is "a person at the door" to the room preventing you from entering - this is the Database Engine, not to be confused with the Database itself. You need to get things out of the room, and you need to put things in the room. You're not allowed to enter. If you want something out of the room, you must ask for it (query) by specifying what the thing looks like and which part of the pile you think it's in. If you want to put something in the room, you must provide explicit instructions for where it must go. When you write SQL, you are either asking a question of or giving instructions to the database engine. The DB Engine is distinct from the Database (the big pile of stuff...which you never have direct access to). (2) As others have pointed out, SQL is very much the mathematics of Set Theory put into a programming language. Stanford has a free class on Databases. I took it a few years ago (it may have changed), and most of it had no SQL at all. It was fairly very straight forward - things you can do with pencil and paper. It's free, so no pressure. Go sign up and do some of the homework assignments. Do them until you get them right (you can re-take wrongly answered problems immediately). It'll help break your procedural mindset. https://online.stanford.edu/courses/soe-ydatabases-databases https://online.stanford.edu/courses/soe-ydatabases-databases

kumarvvr 8y ago
Thank you for the Stanford class reference. Will check it out.
- camel_Snake 8y ago
  I took this one ages ago, before I started programming (in prep for a bootcamp) and really enjoyed it.

thomassantosh 8y ago

Highly recommend Dmitri Fontaine's book "Mastering PostgreSQL in Application Development". Really helps emphasize how you can leverage the power of SQL to minimize all the Python / Panda steps to get the same result.

ddebernardy 8y ago

Reading the Postgres manual cover to cover is a fairly good starting point.

rsyring 8y ago

These mental paradigms helped me a great deal: 1) Think in terms of excel spreadsheets. At each step in your SQL you are creating a set of data. Doesn't usually matter how many rites it is, you just want the right columns. 2) Once you have a recordset, decide how the next recordset goes together. I would mentally visualize putting one worksheet next to the other and think through what columns I had to join together to get the rows I wanted. 3) Make sure you know what your first recordset should be. This is the base data that everything can be joined into. I refer to this as the domain of records.

bashmydotfiles 8y ago

I still struggle with thinking too "proceduraly" sometimes, but what really helps out with me is drawing / envisioning the sets of data as a series of circles and Venn diagrams. It also helps to change the language you use in your inner monologue. Instead of thinking, "For each row in table A...", you should think, "For all the rows in table A that match on...".

zeveshe 8y ago

This book can help with getting into the right mindset: https://www.amazon.com/Joe-Celkos-Thinking-Sets-Management/dp/0123741378 https://www.amazon.com/Joe-Celkos-Thinking-Sets-Management/d...