3.9 Get intersection of all set members of a sequence
We have a table recording percentages of populations who speak different languages in multiple countries. Below is part of the table:
| CountryCode | Language | Percentage |
|---|---|---|
| England | English | 90% |
| America | English | 70% |
| Canada | English | 60% |
| Russia | English | 10% |
| France | English | 5% |
| England | French | 3% |
| America | French | 3% |
| Canada | French | 30% |
| Russia | French | 3% |
| France | French | 95% |
| England | Spanish | 1% |
| America | Spanish | 20% |
| Canada | Spanish | 1% |
| Russia | Spanish | 0.01% |
| France | Spanish | 0.05% |
The task is to list codes of countries where the percentages of populations speaking English, French and Spanish exceed 0.3%, 0.2% and 0.1% respectively.
SPL script:
| A | |
|---|---|
| 1 | =connect(“mysql”) |
| 2 | [English,French,Spanish] |
| 3 | [0.3,0.2,0.1] |
| 4 | =A2.(A1.query@i(“select countrycode from world.countrylanguage where language=? and percentage>?”,~,A3(#))) |
| 5 | >A1.close() |
| 6 | =A4.isect() |
A4 Find codes of countries where percentages of English, French and Spanish populations exceed 0.3%, 0.2% and 0.1% respectively and return result as a sequence. query() function works with @i option to return the selected records as a sequence when they have one column only.
A6 Calculate intersection of A4’s returned sequences.
SPL Official Website 👉 https://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProcSPL
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
