Order-based Calculations – Get a Specified Record and Those Preceding It
【Question】
We have a query like this:
SELECT u.email, st.value as score,
date_format(FROM_UNIXTIME(st.timemodified),'%d-%m-%Y %H:%i:%s' ) as fecha,
IF(st.value >= 70, 'YES', 'NO') as result
FROM user u
LEFT JOIN scorm_scoes_track st ON st.userid = u.id
LEFT JOIN scorm_scoes as ss ON ss.scorm = st.scormid
WHERE (st.element='cmi.score.raw' OR st.element='cmi.core.score.raw')
ORDER BY u.id, st.timemodified
+------------------+------------+---------------------+----------+
| email | score | fecha | result |
+------------------+------------+---------------------+----------+
| test@test.es | 45| 14-03-2015 17:10:18 | NO |
| test@test.es | 65| 14-03-2015 17:12:42 | NO |
| test@test.es | 70| 14-03-2015 17:15:04 | YES |
| test@test.es | 60| 14-03-2015 17:17:16 | NO |
| test@test.es | 65| 14-03-2015 17:17:16 | NO |
User passes the test if result is greater/equal than 70. We need results until the value 70 (when test is passed) is shown. In this case, we have this result:
| test@test.es | 45| 14-03-2015 17:10:18 | NO |
| test@test.es | 65| 14-03-2015 17:12:42 | NO |
| test@test.es | 70| 14-03-2015 17:15:04 | YES |
Records where score >70 are discarded.
【Answer】
Order-based operations are one of SQL weaknesses. Its window functions generate unreadable queries. An alternative is SPL (Structured Process Language) that generates intuitive and easy to understand code:
A |
|
1 |
$select * from tb1 order by score |
2 |
=A1.group(email).conj(~.to(~.pselect(score==70))) |
A1: Data retrieval.
A2: Group email, use conj function to concatenate records and get thoses meeting the specified condition. pselect function gets the sequence number of the first eligible record; to function gets members in a specified interval; “~” represents a group.
esProc SPL can handle almost all complicated intra-group calculations and order-based calculations effortlessly. It can be easily embedded into a Java application via its own JDBC interface. See How to Call an SPL Script in Java.
SPL Official Website 👉 https://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc_SPL
SPL Learning Material 👉 https://c.scudata.com
SPL Source Code and Package 👉 https://github.com/SPLWare/esProc
Discord 👉 https://discord.gg/cFTcUNs7
Youtube 👉 https://www.youtube.com/@esProc_SPL