RaqForum 25 No.
382 View  • 
     ...

3.9 Get intersection of all set members of a sequence

 
spl-cookbook(224)

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-cookbook(224)
 
 •  382 View  •