Päringud äriregistri vähendatud baasist


mysql> EXPLAIN ariregister;
+----------------------------+-------------+------+-----+---------+-------+
| Field                      | Type        | Null | Key | Default | Extra |
+----------------------------+-------------+------+-----+---------+-------+
| nimi                       | varchar(94) | YES  |     | NULL    |       |
| ariregistri_kood           | int(8)      | YES  |     | NULL    |       |
| kmkr_nr                    | varchar(11) | YES  |     | NULL    |       |
| ettevotja_staatus          | varchar(1)  | YES  |     | NULL    |       |
| asukoha_ehak_tekstina      | varchar(53) | YES  |     | NULL    |       |
| indeks_ettevotja_aadressis | varchar(6)  | YES  |     | NULL    |       |
+----------------------------+-------------+------+-----+---------+-------+

SELECT * FROM ariregister LIMIT 5;



+------------------------+------------------+-------------+-------------------+-----------------------+----------------------------+
| nimi                   | ariregistri_kood | kmkr_nr     | ettevotja_staatus | asukoha_ehak_tekstina | indeks_ettevotja_aadressis |
+------------------------+------------------+-------------+-------------------+-----------------------+----------------------------+
| 001 group OÜ           |         12754230 |             | R                 | Tallinn               | 13513                      |
| 007 Autohaus osaühing  |         11694365 | EE101335276 | R                 | Tartu                 | 50603                      |
| 013 Graphics OÜ        |         12624154 | EE101701716 | R                 | Tallinn               | 10616                      |
| 013 investment OÜ      |         12937781 |             | R                 | Tallinn               | 10115                      |
| 01Arvutiabi OÜ         |         14112620 |             | R                 | Tallinn               | 10318                      |
+------------------------+------------------+-------------+-------------------+-----------------------+----------------------------+

Haldusüksuse tulp: asukoha_ehak_tekstina 

Leidke, mitu firmat on registreeritud igasse tabelis olevasse haldusüksusesse

SELECT asukoha_ehak_tekstina, COUNT(*) FROM ariregister GROUP BY asukoha_ehak_tekstina;
| Võrkla küla, Viru-Nigula vald, Lääne-Virumaa              |        1 |
| Vorsti küla, Väike-Maarja vald, Lääne-Virumaa             |        1 |
| Võru                                                      |      199 |
| Võruküla küla, Mäksa vald, Tartumaa                       |        1 |
| Võrumõisa küla, Võru vald, Võrumaa                        |        4 |
| Võsu alevik, Vihula vald, Lääne-Virumaa                   |        4 |
| Võsupere küla, Vihula vald, Lääne-Virumaa                 |        3 |
+-----------------------------------------------------------+----------+


SELECT asukoha_ehak_tekstina, COUNT(*) FROM ariregister GROUP BY asukoha_ehak_tekstina;

SELECT asukoha_ehak_tekstina, COUNT(*) AS kogus 
  FROM ariregister GROUP BY asukoha_ehak_tekstina
  ORDER BY kogus DESC LIMIT 30;


+-----------------------------------------+-------+
| asukoha_ehak_tekstina                   | kogus |
+-----------------------------------------+-------+
| Tallinn                                 | 15832 |
| Tartu                                   |  2032 |
| Pärnu                                   |   779 |
| Narva                                   |   539 |
| Maardu                                  |   307 |
| Kohtla-Järve                            |   291 |
| Rakvere                                 |   283 |
| Viljandi                                |   249 |
|                                         |   228 |
| Kuressaare                              |   205 |
| Võru                                    |   199 |
| Haabneeme alevik, Viimsi vald, Harjumaa |   175 |
| Keila                                   |   165 |
| Peetri alevik, Rae vald, Harjumaa       |   154 |
| Jõhvi, Jõhvi, Ida-Virumaa               |   145 |
| Haapsalu                                |   141 |
| Valga                                   |   137 |
| Sillamäe                                |   136 |
| Saue                                    |   123 |
| Tabasalu alevik, Harku vald, Harjumaa   |   108 |
| Laagri alevik, Saue vald, Harjumaa      |    91 |
| Paide                                   |    89 |
| Põlva, Põlva, Põlvamaa                  |    86 |
| Rapla, Rapla, Raplamaa                  |    85 |
| Saku alevik, Saku vald, Harjumaa        |    82 |
| Viimsi alevik, Viimsi vald, Harjumaa    |    73 |
| Elva                                    |    68 |
| Paldiski                                |    66 |
| Muraste küla, Harku vald, Harjumaa      |    64 |
| Jüri alevik, Rae vald, Harjumaa         |    64 |
+-----------------------------------------+-------+
30 rows in set (1.85 sec)


SELECT asukoha_ehak_tekstina, COUNT(*) AS kogus 
  FROM ariregister GROUP BY asukoha_ehak_tekstina
  HAVING kogus<10 ORDER BY kogus;

CREATE TABLE ariregister_kogused AS  
SELECT asukoha_ehak_tekstina, COUNT(*) AS kogus 
  FROM ariregister GROUP BY asukoha_ehak_tekstina
  HAVING kogus<10 ORDER BY kogus;
  
SELECT * FROM ariregister_kogused LIMIT 10;

SELECT COUNT(*) FROM ariregister_kogused;

SELECT COUNT(*) FROM ariregister_kogused WHERE kogus=1;

SELECT COUNT(*) FROM ariregister_kogused WHERE kogus=2;

SELECT kogus, COUNT(*) AS kylasid FROM ariregister_kogused GROUP BY kogus;
+-------+---------+
| kogus | kylasid |
+-------+---------+
|     1 |    1342 |
|     2 |     547 |
|     3 |     285 |
|     4 |     145 |
|     5 |      85 |
|     6 |      59 |
|     7 |      45 |
|     8 |      30 |
|     9 |      24 |
+-------+---------+

SELECT COUNT(*) FROM ariregister WHERE kmkr_nr="";

+----------+
| COUNT(*) |
+----------+
|    22681 |
+----------+

SELECT COUNT(*) FROM ariregister WHERE NOT kmkr_nr="";

SELECT COUNT(DISTINCT asukoha_ehak_tekstina) FROM ariregister WHERE kmkr_nr="";
+---------------------------------------+
| COUNT(DISTINCT asukoha_ehak_tekstina) |
+---------------------------------------+
|                                  2160 |
+---------------------------------------+
2160s kohas on käibemaksukohustuseta ettevõtjaid.


SELECT COUNT(DISTINCT asukoha_ehak_tekstina) FROM ariregister WHERE NOT kmkr_nr="";

+---------------------------------------+
| COUNT(DISTINCT asukoha_ehak_tekstina) |
+---------------------------------------+
|                                  1466 |
+---------------------------------------+

SELECT asukoha_ehak_tekstina, COUNT(*) AS kogus 
  FROM ariregister WHERE kmkr_nr="" GROUP BY asukoha_ehak_tekstina
  HAVING kogus<10 ORDER BY kogus;

2041 rows in set (0.68 sec) -- niimitmes kohas käibemaksukohustuseta ettevõtjaid,
  kus selles kohas on alla 10 sellise ettevõtja

SELECT asukoha_ehak_tekstina, COUNT(*) AS kogus 
  FROM ariregister WHERE NOT kmkr_nr="" GROUP BY asukoha_ehak_tekstina
  HAVING kogus<10 ORDER BY kogus;
1379 rows in set (0.42 sec)


Mittekohuslaste jaotus

DROP TABLE IF EXISTS ariregister_kogused;

CREATE TABLE ariregister_kogused AS  
SELECT asukoha_ehak_tekstina, COUNT(*) AS kogus 
  FROM ariregister WHERE kmkr_nr="" GROUP BY asukoha_ehak_tekstina
  HAVING kogus<10 ORDER BY kogus;
  
SELECT kogus, COUNT(*) AS kylasid FROM ariregister_kogused GROUP BY kogus;  
  
+-------+---------+
| kogus | kylasid |
+-------+---------+
|     1 |    1177 |
|     2 |     419 |
|     3 |     200 |
|     4 |      93 |
|     5 |      52 |
|     6 |      36 |
|     7 |      28 |
|     8 |      23 |
|     9 |      13 |
+-------+---------+

Kohuslaste jaotus

DROP TABLE IF EXISTS ariregister_kogused;

CREATE TABLE ariregister_kogused AS  
SELECT asukoha_ehak_tekstina, COUNT(*) AS kogus 
  FROM ariregister WHERE NOT kmkr_nr="" GROUP BY asukoha_ehak_tekstina
  HAVING kogus<10 ORDER BY kogus;
  
SELECT kogus, COUNT(*) AS kylasid FROM ariregister_kogused GROUP BY kogus;  

+-------+---------+
| kogus | kylasid |
+-------+---------+
|     1 |     821 |
|     2 |     290 |
|     3 |     103 |
|     4 |      63 |
|     5 |      36 |
|     6 |      25 |
|     7 |      23 |
|     8 |       9 |
|     9 |       9 |
+-------+---------+
mysql> SELECT MIN(ariregistri_kood) FROM ariregister WHERE ariregistri_kood>1000;
+-----------------------+
| MIN(ariregistri_kood) |
+-----------------------+
|              10000018 |
+-----------------------+
1 row in set (0.03 sec)

mysql> SELECT MAX(ariregistri_kood) FROM ariregister WHERE ariregistri_kood>1000;
+-----------------------+
| MAX(ariregistri_kood) |
+-----------------------+
|              90012863 |
+-----------------------+

SELECT ROUND(ariregistri_kood/10000000) AS grupialgus, COUNT(*) FROM ariregister GROUP BY grupialgus;

+------------+----------+
| grupialgus | COUNT(*) |
+------------+----------+
|          0 |        5 |
|          1 |    35203 |
|          8 |     1216 |
|          9 |       18 |
+------------+----------+

Ettevõtete arvu loetelu vastavalt äriregistri koodi algusnumbrile ning käibemaksukohuslusele

SELECT ROUND(ariregistri_kood/10000000) AS grupialgus, kmkr_nr="" AS kohuslus,  COUNT(*) 
   FROM ariregister GROUP BY grupialgus, kohuslus;

+------------+----------+----------+
| grupialgus | kohuslus | COUNT(*) |
+------------+----------+----------+
|          0 |        0 |        2 |
|          0 |        1 |        3 |
|          1 |        0 |    13738 |
|          1 |        1 |    21465 |
|          8 |        0 |       20 |
|          8 |        1 |     1196 |
|          9 |        0 |        1 |
|          9 |        1 |       17 |
+------------+----------+----------+