db_table 초기화

delete from table_name 지우기
ALTER table table_name AUTO_INCREMENT=1; 아이디 값 초기화

django admin

refresh token

sql강의

aggregation-데이터를 하나의 값으로 요약하는 것.

1) 전체 row 수, 결측치 여부 ( COUNT 함수는 Null값을 제외하고 센다는 것이 포인트!!)

mysql> SELECT COUNT(id) from subways;
+-----------+
| COUNT(id) |
+-----------+
|       884 |
+-----------+
1 row in set (0.00 sec)

mysql> SELECT COUNT(id) as id_count from subways;
+----------+
| id_count |
+----------+
|      884 |
+----------+

mysql> SELECT COUNT(*) from subways;
+----------+
| COUNT(*) |
+----------+
|      884 |
+----------+

2. 중복값 확인 가능

mysql> select COUNT(*) as row_count, COUNT(DISTINCT counselor_id)as counselor_count from counselor_kinds;
+-----------+-----------------+
| row_count | counselor_count |
+-----------+-----------------+
|        94 |              55 |
+-----------+-----------------+

3.최대, 최솟값 구하기

mysql> select MAX(monthly_rent) as max_fee, MIN(monthly_rent) as min_fee from rooms_trade_types;
+---------+---------+
| max_fee | min_fee |
+---------+---------+
|  850.00 |    2.00 |
+---------+---------+

4.평균값 구하기

mysql> select AVG(monthly_rent) as average_fee from rooms_trade_types;
+-------------+
| average_fee |
+-------------+
|   76.835465 |
+-------------+

5. 합계 구하기

mysql> select SUM(monthly_rent) as sum from rooms_trade_types;
+-----------+
| sum       |
+-----------+
| 180256.00 |
+-----------+

6. 룸타입(원룸,복층,투룸, 쓰리룸 등)별 평균, 최대, 최소 관리비 구하기(GROUP BY)

mysql> select sub_room_type_id,
    -> AVG(maintenance_fee),
    -> MAX(maintenance_fee),
    -> MIN(maintenance_fee)
    -> from rooms
    -> GROUP BY sub_room_type_id;
+------------------+----------------------+----------------------+----------------------+
| sub_room_type_id | AVG(maintenance_fee) | MAX(maintenance_fee) | MIN(maintenance_fee) |
+------------------+----------------------+----------------------+----------------------+
|                1 |             8.134755 |                40.00 |                 0.00 |
|                2 |            10.630348 |                50.00 |                 0.00 |
|                3 |             8.065882 |                35.00 |                 0.00 |
|                4 |             8.741988 |                50.00 |                 0.00 |
|                5 |            23.300000 |               100.00 |                 0.00 |
+------------------+----------------------+----------------------+----------------------+

7. group by 더보기
   group by로 쪼개는 기준내용은 select 뒤에도 꼭 써줘야한다.

아래처럼 쓸수도 있다.


8. where vs having
where: 개별 행에 적용
having: 그룹화된 결과에 적용 (group by로 얻은 결과에는 having 써아햠)

9.끝판왕 정리
SELECT (원하는 컬럼)
FROM (조회하는 테이블)
WHERE (GROUP BY 이전 필터링할 조건)
GROYP BY (묶어서 보고 싶은 컬럼)
HAVING (GROUP BY 이후 필터링 조건)
ORDER BY (정렬해서 보고 싶은 컬럼)
LIMIT (상위n개 결과만 조회

예시)

SELECT neighborhood_group,
neighborhood,
COUNT(id) AS id_count,
AVG(price) AS average_price
FROM airbnb_nyc
GROUP BY neighborhood_group,
neighborhood
HAVING AVG(price) < 150
ORDER BY neighborhood_group,
COUNT(id) DESC

출처: 탈잉 단비님 데이터분석 강의