데이터베이스 질문과 답변
안녕하세요.
최근 새로운 프로젝트를 시작하면서 유저 테이블과 거래 테이블의 primary key를 가지고
이리 저리 데이터를 조인해가면서 프로젝트를 진행해가면서 동시에 관계형 데이터베이스를 공부하던 중
체계적으로 배운적이 없다보니 SQL 쪽은 말그대로 다른 개발자분들의 소스를 습작해서 쓰다시피해와서
한계를 느껴 이렇게 글을 쓰게 되었습니다. 고수분들의 조언을 부탁합니다.
아래는 진행중인 프로젝트의 데이터베이스 테이블의 일부입니다.
거래테이블 입니다
(Primary Key 는 id 컬럼입니다)
id | equip_type | created_at | updated_at | -----+------------|------------|------------| 1 | 3 | 2021-05-09 | 2021-05-09 | 2 | 3 | 2021-05-09 | 2021-05-09 | 3 | 3 | 2021-05-09 | 2021-05-09 | 4 | 2 | 2021-05-09 | 2021-05-09 | 5 | 2 | 2021-05-09 | 2021-05-09 | 아래는 잔액 테이블입니다. Foreign Key 는 trans_id 컬럼에 설정되있고 거래 테이블의 id컬럼을 참조합니다. 하나 더 있는 Foreign Key 는 user_id 컬럼이고 이건 유저 정보테이블의 id컬럼을 참조합니다.
trans_id | user_id | prev_balance | balance |
-----------+------------|--------------|------------|
1 | 3 | 20000.000 | 50000.000 |
2 | 3 | 10000.000 | 20000.000 |
3 | 3 | 20000.000 | 10000.000 |
4 | 2 | 2345.000 | 6000.000 |
5 | 2 | 1233.000 | 3000.000 |
유저 정보테이블입니다.
Primary Key는 id 컬럼입니다.
id | nickname | -----+------------| 1 | jane | 2 | john | 3 | beer | 4 | mac | 5 | star |
이렇게 구성된 데이터베이스에서 세개의 테이블을 조인하여 가져오는것에 더해
기간을 정해서 최종 데이터 또는 일별 최종 데이터를 쿼리를 하려고 하니 한계를 절실히 느낍니다.
제가 만들려고 한 쿼리 결과는 이렇습니다.
1. 일별조회 결과
id | equip_type | nickname | balance | created_at | updated_at | -----+------------|--------------|------------|------------|------------| 1 | 3 | beer | 50000.000 | 2021-05-09 | 2021-05-09 | 4 | 2 | john | 6000.000 | 2021-05-09 | 2021-05-09 |
2. 기간별 조회 결과
id | equip_type | nickname | balance | created_at | updated_at | -----+------------|--------------|------------|------------|------------| 1 | 3 | beer | 10000.000 | 2021-05-05 | 2021-05-05 | 4 | 2 | john | 1233.000 | 2021-05-04 | 2021-05-04 | 6 | 2 | mac | 300.000 | 2021-05-02 | 2021-05-02 | 7 | 2 | star | 200.000 | 2021-05-01 | 2021-05-01 |
예전엔 고민이 필요하거나 방법이 없는 복잡한 쿼리문은 이해도 어렵고 작성도 어려워 시간이 없다는 핑계로 select 걸어 반복문으로
배열에 담고 다시 쿼리하거나 컬럼만 무한생성해 셀렉트 거는식으로 개발해온 덕에 이런 상황이 온것같습니다.
이런 경우 어떤식으로 쿼리문을 작성해야하는지 조언해 주시면 너무나 감사드리겠습니다.
1. 일별 마지막 값을 조회하려면
- 일자(date)만 있으면 안되고 일시(datetime)가 있어야 합니다.
- 예시 자료엔 시간이 표시가 안되고 있네요?
2. 그룹 기준이 불분명 합니다.
- ID 별 일별 마지막값인데?
- ID 가 뭔 ID 인가요? ( trans_id ? equip_type ? user_id ? )
3. 마지막 값을 구하려면?
- ROW_NUMBER 를 이용하시면 됩니다.
- http://gurubee.net/lecture/2672
SELECT *
FROM (SELECT b.trans_id
, a.equip_type
, b.user_id
, c.nickname
, b.balance
, a.created_at
, a.updated_at
, ROW_NUMBER() OVER(PARTITION BY b.user_id ORDER BY a.created_at DESC) rn
FROM equip a
INNER JOIN balance b
ON a.id = b.trans_id
INNER JOIN user c
ON b.user_id = c.id
WHERE a.created_at >= '2021-05-01'
AND a.created_at < '2021-05-09' + INTERVAL 1 DAY
) d
WHERE rn = 1
;
마농님 감사합니다
사실 예시에는 말씀데로 date만 있는것이 아닌 datetime으로 되어있습니다 제가 예시 표시를 잘못했습니다.
id별 일별 마지막값은 user_id입니다 설명이 부족했습니다.
헌데 이해하시고 설명해주신것같아 너무 감사합니다.
뭔가 해답을 찾아가는것 같아 정신이 번쩍드네요.
현답에 많은것을 배워갑니다 감사합니다
) 버튼을 클릭하여 작성 하시면 됩니다.