mysql 각 행에 한 테이블의 데이터를 다른 테이블과 결합

mysql 각 행에 한 테이블의 데이터를 다른 테이블과 결합

아래 표에서 특정 날짜 범위에 대한 보고서를 생성하려고 했습니다.

table_columns =>   employee_id |date | status 

여기서 상태 1 = not_pending, 2 = visited, 3 = 취소, 4 = pending (승인 대기 중)리포트는 다음과 같습니다.

+-------------+------------+-------+-------------+---------+----------+---------+
| employee_id | date       | total | not_visited | visited | canceled | pending |
+-------------+------------+-------+-------------+---------+----------+---------+
|           3 | 2021-06-01 |    10 |          10 |       0 |        0 |       0 |
|           3 | 2021-06-02 |    22 |          10 |       2 |       10 |       0 |
|           3 | 2021-06-03 |    10 |          10 |       0 |        0 |       0 |
|           3 | 2021-06-05 |    11 |          10 |       1 |        0 |       0 |
|           4 | 2021-06-01 |    11 |           8 |       3 |        0 |       0 |
|           5 | 2021-06-01 |    10 |           1 |       9 |        0 |       0 |
+-------------+------------+-------+-------------+---------+----------+---------+

이 보고서의 쿼리는 다음과 같습니다.

select va.employee_id, va.date,
       count(*) as total,
       sum(case when status = 1 then 1 else 0 end) as not_visited,
       sum(case when status = 2 then 1 else 0 end) as visited,
       sum(case when status = 3 then 1 else 0 end) as canceled,
       sum(case when status = 4 then 1 else 0 end) as pending
from visiting_addresses va
where va.date >= '2021-06-01'
  and va.date <= '2021-06-30'
group by va.employee_id, va.date;

결과를 보면 날짜 항목이 없습니다.2021-06-04employee_id = 3인 경우.2021-06-06부터 2021-06-30까지는 데이터가 없습니다.결과에 이 날짜를 포함시켜야 합니다.그래서 지정된 범위 사이의 날짜를 생성하는 다른 쿼리를 생성하려고 했습니다.다음 쿼리로 그렇게 할 수 있습니다.

SELECT gen_date
  FROM
    (SELECT v.gen_date
       FROM
         (SELECT ADDDATE('1970-01-01',t4 * 10000 + t3 * 1000 + t2 * 100 + t1 * 10 + t0) gen_date
            FROM
              (SELECT 0 t0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION 
       SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION
           SELECT 8 UNION SELECT 9) t0,
          (SELECT 0 t1 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION
           SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION
           SELECT 8 UNION SELECT 9) t1,
          (SELECT 0 t2 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION
           SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION
           SELECT 8 UNION SELECT 9) t2,
          (SELECT 0 t3 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION
           SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION
           SELECT 8 UNION SELECT 9) t3,
          (SELECT 0 t4 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION
           SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION
           SELECT 8 UNION SELECT 9) t4
          ) v
    WHERE v.gen_date BETWEEN '2021-06-01' AND '2021-06-30'
 ) calendar;

이 쿼리는 다음과 같은 날짜를 생성합니다.

+------------+
| gen_date   |
+------------+
| 2021-06-01 |
| 2021-06-02 |
| 2021-06-03 |
| .......... |
| ...........|
| 2021-06-27 |
| 2021-06-28 |
| 2021-06-29 |
| 2021-06-30 |
+------------+

여기서 문제는 위의 두 가지 쿼리에 어떻게 참여하면 각 employee_id에 대해 결과에 모든 날짜가 표시되도록 할 수 있는가 하는 것입니다.아니면 이런 식으로도 가능합니까? (실제 표에는 500만 행이 포함되어 있습니다.)employee_id 열의 카디널리티는 3k++이고 날짜 및 employee_id 열은 인덱싱됩니다.)

MySQL과 MariaDB를 모두 태그했습니다.이 2개의 DBMS는 친척 관계이지만, 아직 다른 DBMS입니다.MariaDB에서는 빌트인을 통해 쉽게 시리즈를 생성할 수 있습니다.seq:

select date '2021-06-01' + interval seq day as date from seq_0_to_29

MySQL에서는 이 쿼리를 사용할 수 없으며 다음과 같은 경우 재귀 쿼리를 사용할 수 있습니다.

with recursive dates (date) as
(
  select date '2021-06-01'
  union all
  select date + interval 1 day
  from dates
  where date < date '2021-06-30'
)

재귀 쿼리에서는 물론 테이블의 마지막 달 또는 현재 및 이전 달 등의 날짜를 동적으로 생성할 수 있습니다.

모든 SQL 방언에서 조회에 참여할 수 있습니다.이 경우 모든 날짜(표시된 대로 생성됨)를 모든 직원(직원 테이블에서 선택)과 결합하거나 visiting_addresses 테이블에 있는 직원만 결합할 수 있습니다.테이블에 데이터가 있는 직원만 원하는 경우 다음을 사용하십시오.

select distinct employee_id from visiting_addresses

모든 조합을 얻으려면 두 데이터 세트를 교차 결합해야 합니다.그런 다음 직원/데이트를 방문 없이 유지하기 위해 테이블의 데이터를 외부에서 결합합니다.

쿼리 형식은 다음과 같습니다.

select
  employees.employee_id,
  dates.date,
  visits.total,
  visits.not_visited,
  ...
from ( <date sequence query here> ) dates
cross join ( <employee table query here> ) employees
left outer join ( <visits table query here> ) visits
  on visits.date = dates.date
  and visits.employee_id = employees.employee_id
order by employees.employee_id, dates.date;

(모든 종업원을 대상으로 하고 싶은 경우는, 간단하게 교환해 주세요.( <employee table query here> ) employees테이블 이름만으로employees.

가독성을 확보하기 위해WITH절:

with recursive dates (date) as ( <date sequence query here> )
   , employees as ( <employee table query here> )
   , visits as ( <visits table query here> )
select 
  employees.employee_id,
  dates.date,
  visits.total,
  visits.not_visited,
  ...
from  dates
cross join employees
left outer join visits
  on visits.date = dates.date
  and visits.employee_id = employees.employee_id
order by employees.employee_id, dates.date;

테이블이 꽤 크다고 말씀하셨는데요.이 쿼리에 대해 다음 인덱스를 권장합니다.

create index idx on visiting_addresses (date, employee_id, status);

언급URL : https://stackoverflow.com/questions/70843708/mysql-joining-one-tables-data-with-other-tables-each-row