1. PostgreSQL에서 JSONB 데이터 평균 구하기JSONB 컬럼이 포함된 테이블에서 특정 키(예: "efficiency") 값을 필터링하고 평균을 구하는 기본적인 SQL 쿼리는 다음과 같습니다.SELECT key AS tag_name, AVG(value::NUMERIC) AS avg_valueFROM sensor_data, LATERAL jsonb_each_text(outputs)WHERE timestamp_col BETWEEN '2024-01-01 00:00:00' AND '2024-01-31 23:59:59' AND key ILIKE '%efficiency%' AND (value::NUMERIC) BETWEEN 0 AND 100GROUP BY key;이 쿼리는..

PostgreSQL에서 최댓값을 구하는 방법은 MAX() 함수와 ORDER BY ... DESC LIMIT 1 방식이 있습니다. 각각의 차이점과 성능을 비교해보겠습니다.1. MAX() 함수 사용최댓값을 구할 때 MAX()를 사용하면 쉽게 값을 찾을 수 있습니다.SELECT MAX(column_name) FROM table_name;장점최댓값을 빠르게 조회할 수 있음정렬이 필요하지 않음단점해당 값을 가진 행을 가져오려면 서브쿼리를 사용해야 함SELECT * FROM table_name WHERE column_name = (SELECT MAX(column_name) FROM table_name);2. ORDER BY DESC LIMIT 1 사용최댓값을 포함한 행을 가져올 때 유용합니다.SELECT * FRO..

1. DISTINCT ON 사용 (PostgreSQL 전용)PostgreSQL에서는 DISTINCT ON을 사용하면 간단하게 해결할 수 있습니다.SELECT DISTINCT ON (tagname) tagname, status, timestampFROM your_tableORDER BY tagname, timestamp DESC;설명DISTINCT ON (tagname)을 사용하여 각 tagname별로 첫 번째 행만 선택합니다.ORDER BY tagname, timestamp DESC를 지정하여 최신 timestamp 값이 먼저 오도록 정렬합니다.최신 데이터의 status 값을 가져올 수 있습니다.2. 윈도우 함수 사용 (다른 DBMS에서도 활용 가능)PostgreSQL 외의 다른 DBMS에서도 적용할 수..

PostgreSQL에서 테이블 A의 특정 컬럼 값이 B 또는 C 테이블에 존재하는지 여부를 조회할 때, 이를 'O' 또는 'X'로 표시하는 방법을 소개합니다.1. 문제 정의테이블 A의 특정 컬럼 값이 B 또는 C 테이블에 존재하는지 확인하고, 결과에서만 이를 'O' 또는 'X'로 표시하려고 합니다.2. SQL 쿼리 작성다음은 EXISTS 서브쿼리와 CASE 문을 활용한 SQL 쿼리입니다.SELECT A.*, CASE WHEN EXISTS (SELECT 1 FROM B WHERE B.target_column = A.target_column) THEN 'O' ELSE 'X' END AS is_in_b, CASE WHEN EXISTS (SELECT 1..

PostgreSQL에서 기존 데이터가 있는 상태에서 .tar 형식의 백업 파일을 pg_restore 명령어로 복원하면 여러 가지 상황이 발생할 수 있습니다. 데이터 충돌을 방지하고 안전하게 복원하는 방법은 아래와 같습니다.1. .tar 백업 파일 복원 방법1.1 기존 데이터를 유지하면서 복원 (추가/병합)pg_restore -U username -d dbname backup.tar기본적으로 기존 데이터는 유지되며, 백업된 데이터가 추가됩니다.동일한 PRIMARY KEY나 UNIQUE 값이 있는 경우 오류가 발생할 수 있습니다.1.2 기존 데이터를 삭제하고 복원 (초기화 후 복원)기존 테이블/데이터 삭제 후 복구 (--clean 옵션)pg_restore -U username -d dbname --clean..