Əsas səhifə > MySQL > Emulating Oracle’s analytic function OVER() in MySQL

Emulating Oracle’s analytic function OVER() in MySQL

Oracle-da olub MySQL-də olmayan daha bir gözəl funksionallıq analytic funksiyalardır və onlardan çox xoşuma gələn:
Function(arg1,…, argn) OVER ( [PARTITION BY ] [ORDER BY ] [] )

Bu funksiya nə edir? Deyək ki, aşağıdakı cədvəlimiz var:

create table company (
    id tinyint not null auto_increment,
    com_group varchar(25) not null,
    com_name varchar(30) not null,
    emp_count int not null,
    primary key (id)
);

Və cədvəldə aşağıdakı məlumatlar var:

insert into company(com_group,com_name,emp_count) values('Tikinti','Yol-tikinti',20),('Tikinti','Bina-tikinti',30),('Tikinti','Korpu-tikinti',80);
insert into company(com_group,com_name,emp_count) values('Senaye','kimya-senaye-asc',40),('Senaye','metallurgiya-asc',500);
insert into company(com_group,com_name,emp_count) values('Xidmet','sosial-mudafie-asc',100),('Xidmet','ictimai-yardimc-asc',200);
insert into company(com_group,com_name,emp_count) values('Neft','azeriqaz',2000),('Neft','derin-ozuller',300),('Neft','gunesli',130);

Tapşırıq belədir ki, ~Hər müəssisənin işçi sayının ümümu müəssisə qrupunda olan işçi sayına nisbətini tapın~. Yəni, bizdə müəssisə qrupu Tikinti-dir və bu tikinti qrupunda toplam 3 müəssisə var. Hər müəssisədə 20,30 və 80 işçi çalışır. Toplam olaraq 130. bizə lazımdır ki, hər müəssisədə olan işçi sayını toplam işçi sayına bölərək nisbəti tapaq. 20/130, 30/130, 80/130 və.s bu şəkildə davam etsin.
Oracle-da bunu çox rahatlıqla yerinə yetirmək olur:

select id,com_group,com_name,emp_count,emp_count/sum(emp_count)
over (partition by com_group) from company;

Nəticədə isə aşağıdakı məlumatlar çıxmalıdır(Oracle installation-um mövcud olmadığı üçün test etməmişəm)

+----+-----------+---------------------+-----------+--------+
| id | com_group | com_name            | emp_count | nisbet |
+----+-----------+---------------------+-----------+--------+
|  1 | Tikinti   | Yol-tikinti         |        20 | 0.1538 |
|  2 | Tikinti   | Bina-tikinti        |        30 | 0.2308 |
|  3 | Tikinti   | Korpu-tikinti       |        80 | 0.6154 |
|  4 | Senaye    | kimya-senaye-asc    |        40 | 0.0741 |
|  5 | Senaye    | metallurgiya-asc    |       500 | 0.9259 |
|  6 | Xidmet    | sosial-mudafie-asc  |       100 | 0.3333 |
|  7 | Xidmet    | ictimai-yardimc-asc |       200 | 0.6667 |
|  8 | Neft      | azeriqaz            |      2000 | 0.8230 |
|  9 | Neft      | derin-ozuller       |       300 | 0.1235 |
| 10 | Neft      | gunesli             |       130 | 0.0535 |
+----+-----------+---------------------+-----------+--------+
10 rows in set (0.00 sec)

Çox gözəl Oracle bacarır ))) MySQL-də eyni nəticəni biraz daha əziyyətli üsulla almaq olur.
İlk öncə ağıla gələn ilk üsul:

select 
    com_1.id,
    com_1.com_group,
    com_1.com_name,
    com_1.emp_count,
    (com_1.emp_count/(select sum(emp_count) from company  group by com_group)) as nisbet
from company as com_1;

ERROR 1242 (21000): Subquery returns more than 1 row

Təbriklər alınmadı )) Lakin biraz dəyişiklik etdikdə istədiyimiz nəticəni qismən əldə etmiş oluruq:

select 
    com_1.id,
    com_1.com_group,
    com_1.com_name,
    com_1.emp_count,
    (com_1.emp_count/(select sum(emp_count) from company  where com_group='Tikinti' group by com_group)) as nisbet
from company as com_1 where com_group='Tikinti';
+----+-----------+---------------+-----------+--------+
| id | com_group | com_name      | emp_count | nisbet |
+----+-----------+---------------+-----------+--------+
|  1 | Tikinti   | Yol-tikinti   |        20 | 0.1538 |
|  2 | Tikinti   | Bina-tikinti  |        30 | 0.2308 |
|  3 | Tikinti   | Korpu-tikinti |        80 | 0.6154 |
+----+-----------+---------------+-----------+--------+
3 rows in set (0.00 sec)

Və hər bir com_group üçün ayrılıqda bu nəticəni tapıb union all etsək yuxarıdakı nəticəni tamamilə almış olacıq:

(select 
    com_1.id,
    com_1.com_group,
    com_1.com_name,
    com_1.emp_count,
    (com_1.emp_count/(select sum(emp_count) from company  where com_group='Tikinti' group by com_group)) as nisbet
from company as com_1 where com_group='Tikinti')
union all
(select 
    com_1.id,
    com_1.com_group,
    com_1.com_name,
    com_1.emp_count,
    (com_1.emp_count/(select sum(emp_count) from company  where com_group='Senaye' group by com_group)) as nisbet
from company as com_1 where com_group='Senaye')
union all
(select 
    com_1.id,
    com_1.com_group,
    com_1.com_name,
    com_1.emp_count,
    (com_1.emp_count/(select sum(emp_count) from company  where com_group='Xidmet' group by com_group)) as nisbet
from company as com_1 where com_group='Xidmet')
union all
(select 
    com_1.id,
    com_1.com_group,
    com_1.com_name,
    com_1.emp_count,
    (com_1.emp_count/(select sum(emp_count) from company  where com_group='Neft' group by com_group)) as nisbet
from company as com_1 where com_group='Neft');

+----+-----------+---------------------+-----------+--------+
| id | com_group | com_name            | emp_count | nisbet |
+----+-----------+---------------------+-----------+--------+
|  1 | Tikinti   | Yol-tikinti         |        20 | 0.1538 |
|  2 | Tikinti   | Bina-tikinti        |        30 | 0.2308 |
|  3 | Tikinti   | Korpu-tikinti       |        80 | 0.6154 |
|  4 | Senaye    | kimya-senaye-asc    |        40 | 0.0741 |
|  5 | Senaye    | metallurgiya-asc    |       500 | 0.9259 |
|  6 | Xidmet    | sosial-mudafie-asc  |       100 | 0.3333 |
|  7 | Xidmet    | ictimai-yardimc-asc |       200 | 0.6667 |
|  8 | Neft      | azeriqaz            |      2000 | 0.8230 |
|  9 | Neft      | derin-ozuller       |       300 | 0.1235 |
| 10 | Neft      | gunesli             |       130 | 0.0535 |
+----+-----------+---------------------+-----------+--------+
10 rows in set (0.02 sec)

Çox uzun və dəhşətli üsul olmağına baxmyaraq işləyir. Daha 2 üsul isə tamamilə bizim işimizə yarayacaq:

select 
    com_1.id,
    com_1.com_group,
    com_1.com_name,
    com_1.emp_count,
    (com_1.emp_count / com_2.sum) as nisbet
from
    company as com_1
        inner join
    (select com_group, sum(emp_count) as sum from company group by com_group) as com_2
 ON com_1.com_group = com_2.com_group;

+----+-----------+---------------------+-----------+--------+
| id | com_group | com_name            | emp_count | nisbet |
+----+-----------+---------------------+-----------+--------+
|  1 | Tikinti   | Yol-tikinti         |        20 | 0.1538 |
|  2 | Tikinti   | Bina-tikinti        |        30 | 0.2308 |
|  3 | Tikinti   | Korpu-tikinti       |        80 | 0.6154 |
|  4 | Senaye    | kimya-senaye-asc    |        40 | 0.0741 |
|  5 | Senaye    | metallurgiya-asc    |       500 | 0.9259 |
|  6 | Xidmet    | sosial-mudafie-asc  |       100 | 0.3333 |
|  7 | Xidmet    | ictimai-yardimc-asc |       200 | 0.6667 |
|  8 | Neft      | azeriqaz            |      2000 | 0.8230 |
|  9 | Neft      | derin-ozuller       |       300 | 0.1235 |
| 10 | Neft      | gunesli             |       130 | 0.0535 |
+----+-----------+---------------------+-----------+--------+
10 rows in set (0.00 sec)

və biraz daha kompakt üsul:

select x.com_group, x.com_name, x.emp_count / sum(y.emp_count)
from 
(select * from company) as x 
inner join
(select  * from company) as y
on x.com_group = y.com_group
group by x.com_group,x.emp_count
order by x.id;

+-----------+---------------------+--------------------------------+
| com_group | com_name            | x.emp_count / sum(y.emp_count) |
+-----------+---------------------+--------------------------------+
| Tikinti   | Yol-tikinti         |                         0.1538 |
| Tikinti   | Bina-tikinti        |                         0.2308 |
| Tikinti   | Korpu-tikinti       |                         0.6154 |
| Senaye    | kimya-senaye-asc    |                         0.0741 |
| Senaye    | metallurgiya-asc    |                         0.9259 |
| Xidmet    | sosial-mudafie-asc  |                         0.3333 |
| Xidmet    | ictimai-yardimc-asc |                         0.6667 |
| Neft      | azeriqaz            |                         0.8230 |
| Neft      | derin-ozuller       |                         0.1235 |
| Neft      | gunesli             |                         0.0535 |
+-----------+---------------------+--------------------------------+
10 rows in set (0.03 sec)

Təşəkkürlər😉

  1. İyun 21, 2013 tarixində, saat 06:47

    gözəl təşəkkürlər🙂

  1. No trackbacks yet.

Bir cavab yazın

Sistemə daxil olmaq üçün məlumatlarınızı daxil edin və ya ikonlardan birinə tıklayın:

WordPress.com Loqosu

WordPress.com hesabınızdan istifadə edərək şərh edirsinz. Çıxış / Dəyişdir )

Twitter rəsmi

Twitter hesabınızdan istifadə edərək şərh edirsinz. Çıxış / Dəyişdir )

Facebook fotosu

Facebook hesabınızdan istifadə edərək şərh edirsinz. Çıxış / Dəyişdir )

Google+ foto

Google+ hesabınızdan istifadə edərək şərh edirsinz. Çıxış / Dəyişdir )

%s qoşulma

%d bloqqer bunu bəyənir: