Arvukomplektide võrdlemine Eri põlvkondade režissööride esimese filmi tegemise vanuse võrdlemine Alates 1985 sündinud Enne 1955 sündinud Ideid: Millal jõuti esimese mängufilmini režissöörina (kahe ajavahemiku võrdlus) Kahe filmitüübi näitlejate vanus operaatorite ( FILM_MAKER_CINEMATOGRAPHER classificator_code film_maker) MariaDB [mart]> explain efis_film_maker; +--------------------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------------+------------------+------+-----+---------+----------------+ | id | int(11) unsigned | NO | PRI | NULL | auto_increment | | film_id | int(11) unsigned | NO | MUL | NULL | | | classificator_code | varchar(100) | NO | | NULL | | | people_id | int(11) unsigned | NO | MUL | NULL | | | seq | int(11) | NO | | NULL | | | created | datetime | NO | | NULL | | | created_by | int(11) unsigned | NO | | NULL | | +--------------------+------------------+------+-----+---------+----------------+ MariaDB [mart]> explain efis_people; +-------------------+---------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------------+---------------------+------+-----+---------+----------------+ | id | int(11) unsigned | NO | PRI | NULL | auto_increment | | first_name | varchar(45) | NO | | NULL | | | last_name | varchar(45) | YES | | NULL | | | date_of_birth | datetime | YES | | NULL | | | date_of_death | datetime | YES | | NULL | | | is_visible | tinyint(1) unsigned | NO | | 1 | | | num_related_films | int(11) unsigned | NO | | 0 | | | notes | text | YES | | NULL | | | created | datetime | NO | | NULL | | | created_by | int(11) | NO | | NULL | | | updated | datetime | NO | | NULL | | | updated_by | int(11) | NO | | NULL | | +-------------------+---------------------+------+-----+---------+----------------+ +------------------------------+---------------------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------------------------+---------------------------------+------+-----+---------+----------------+ | id | int(11) unsigned | NO | PRI | NULL | auto_increment | | film_seq_id | int(11) unsigned | NO | MUL | NULL | | | film_version_number | int(11) unsigned | NO | | NULL | | | production_year | varchar(50) | NO | | NULL | | | profile_image_filename | varchar(255) | YES | | NULL | | | copyright_type | int(11) unsigned | NO | | NULL | | | copyright_register_info | varchar(255) | NO | | NULL | | | copyright_note_on_movie | varchar(255) | NO | | NULL | | | archive_standard_number | varchar(50) | NO | | NULL | | | archive_kriimsilm_id | varchar(50) | NO | | NULL | | | archive_movie_archive_url | varchar(255) | YES | | NULL | | | archive_err_url | varchar(255) | YES | | NULL | | | archive_arrival_date | varchar(255) | YES | | NULL | | | archive_handover_person_name | varchar(50) | NO | | NULL | | | archive_fond_name | varchar(100) | NO | | NULL | | | archive_fond_nr | varchar(25) | NO | | NULL | | | archive_archival_document_nr | varchar(25) | NO | | NULL | | | archive_usage_copy | varchar(50) | NO | | NULL | | | archive_usage_format | varchar(50) | NO | | NULL | | | archive_usage_start_date | varchar(50) | NO | | NULL | | | archive_usage_end_date | varchar(50) | NO | | NULL | | | archive_access_restriction | varchar(200) | NO | | NULL | | | archive_additional_material | text | NO | | NULL | | | record_info | text | NO | | NULL | | | record_sources | text | NO | | NULL | | | record_creator_opinion | text | NO | | NULL | | | efa_editor_opinion | text | NO | | NULL | | | efa_editor_status | varchar(50) | NO | | NULL | | | is_latest | tinyint(1) | NO | | NULL | | | is_visible | tinyint(1) | NO | | 1 | | | status_by | int(11) unsigned | NO | | NULL | | | status | enum('active','new','archived') | NO | | NULL | | | status_datetime | datetime | NO | | NULL | | | updated | datetime | NO | | NULL | | | updated_by | int(11) unsigned | NO | | NULL | | | created | datetime | NO | | NULL | | | created_by | int(11) unsigned | NO | | NULL | | +------------------------------+---------------------------------+------+-----+---------+----------------+ SELECT last_name, YEAR(date_of_birth), production_year FROM efis_film_maker JOIN efis_people ON efis_film_maker.people_id=efis_people.id JOIN efis_film ON efis_film_maker.film_id=efis_film.id LIMIT 10; +-----------+---------------------+-----------------+ | last_name | YEAR(date_of_birth) | production_year | +-----------+---------------------+-----------------+ | Vainokivi | 1964 | 2001 | | Vainokivi | 1964 | 2001 | | Lepp | 1961 | 2001 | | Vainokivi | 1964 | 2001 | | Vainokivi | 1964 | 2001 | | Vainokivi | 1964 | 2001 | | Heinlo | NULL | 2001 | | Saks | NULL | 2001 | | Tenso | 1968 | 2001 | | Leesik | NULL | 2001 | +-----------+---------------------+-----------------+ SELECT first_name, last_name, YEAR(date_of_birth), MIN(production_year) FROM efis_film_maker JOIN efis_people ON efis_film_maker.people_id=efis_people.id JOIN efis_film ON efis_film_maker.film_id=efis_film.id GROUP BY efis_people.id LIMIT 10; +------------+-----------+---------------------+----------------------+ | first_name | last_name | YEAR(date_of_birth) | MIN(production_year) | +------------+-----------+---------------------+----------------------+ | Martin | Aadamsoo | 1975 | 2009 | | Ülev | Aaloe | 1944 | 1979 | | Sigrid | Aamisepp | NULL | 1966 | | Maimo | Aanja | NULL | 1974 | | Meelis | Aanja | NULL | 1978 | | Aino | Aarde | NULL | 1973 | | Juhan | Aare | 1948 | 1986 | | Tõnu | Aare | 1953 | 1977 | | Jüri | Aarma | 1951 | 2002 | | Anneli | Aarma | NULL | 1985 | +------------+-----------+---------------------+----------------------+ SELECT first_name, last_name, YEAR(date_of_birth), MIN(production_year), MIN(production_year)-YEAR(date_of_birth) AS esmavanus FROM efis_film_maker JOIN efis_people ON efis_film_maker.people_id=efis_people.id JOIN efis_film ON efis_film_maker.film_id=efis_film.id WHERE YEAR(date_of_birth) IS NOT NULL AND length(production_year)=4 AND efis_film_maker.classificator_code='FILM_MAKER_DIRECTOR' GROUP BY efis_people.id LIMIT 10; --joonistage XY-diagramm sünniaasta ja esmase režissööriks oldud filmi vanuse vahel +------------+-----------+---------------------+----------------------+ | first_name | last_name | YEAR(date_of_birth) | MIN(production_year) | +------------+-----------+---------------------+----------------------+ | Martin | Aadamsoo | 1975 | 2009 | | Ülev | Aaloe | 1944 | 1979 | | Juhan | Aare | 1948 | 1986 | | Tõnu | Aare | 1953 | 1977 | | Jüri | Aarma | 1951 | 2002 | | Heikki | Aasaru | 1949 | 1980 | | Lauri | Aaspõllu | 1964 | 1985 | | Tõnu | Aav | 1939 | 1964 | | Karl | Ader | 1903 | 1964 | | Mait | Agu | 1951 | 1981 | +------------+-----------+---------------------+----------------------+ tudengifilm- 33 SELECT first_name, last_name, YEAR(date_of_birth) AS synniaasta, MIN(production_year) AS esmafilmiaasta, MIN(production_year)-YEAR(date_of_birth) AS esmavanus FROM efis_film_maker JOIN efis_people ON efis_film_maker.people_id=efis_people.id JOIN efis_film ON efis_film_maker.film_id=efis_film.id JOIN efis_film_type_rel ON efis_film_type_rel.film_id=efis_film.id WHERE YEAR(date_of_birth) IS NOT NULL AND length(production_year)=4 AND efis_film_maker.classificator_code='FILM_MAKER_DIRECTOR' AND NOT efis_film_type_rel.type_id=33 GROUP BY efis_people.id