What is so cool in analytic functions?

Analytic functions give you possibility to look at previous and following rows in a row set without expensive and often impossible joins because there are times you don’t know how many joins you’ll need. With analytics you can simply look for a value (or compute it using) either foregone or calculated rows forward or backwards. That means Oracle can scan source data once and then sort it (if necessary more than once) in memory or temp. My experience shows that sorting even several times is much faster than scanning source data more than once. In next four cases I’ll try to show four different real world reports that exploited analytic functions a lot.

Case one: persons and their citizenships

Report basically counted people and their citizenships. Mostly people have only one citizenship, but those who are agile enough get more than one. Respectively report stated following algorithm:

1) If person has citizenship X (most of the people has it) then is counted it and only it regardless of all other his citizenships;

2) If person hasn’t citizenship X then are counted all his citizenships.

Table PERSONS contains each person along with its name and surname. Table CITIZENSHIPS is joined to PERSONS and each person’s citizenship has one row in CITIZENSHIPS. So how to get the report using algorithm defined above?