복붙노트

[SQL] 어떻게 다양한 형식 SQLite는의 피벗 또는 즉 선택에 표는 긴 형식으로 저장?

SQL

어떻게 다양한 형식 SQLite는의 피벗 또는 즉 선택에 표는 긴 형식으로 저장?

나는 긴 형식과 그들이 한 쿼리에서 자신의 주제의 모든받는 부호로 학생들의 데이터를 저장하는 테이블을 좀하고 싶습니다.

이것은 내 테이블 구조입니다 :

테이블 : 시장 정보

## studid ## ## subjectid ##  ## marks ##
     A1            3                50
     A1            4                60
     A1            5                70
     B1            3                60
     B1            4                80
     C1            5                95

테이블 : 학생 정보

실제 구조는 :

## studid ##  ## name ##
      A1          Raam
      B1          Vivek
      c1          Alex

나는 pivotization의 결과로 다음과 같은 다양한 형식의 구조로 결과 집합을 원하는 :

테이블 : 학생 정보

## studid ## ## name## ## subjectid_3 ## ## subjectid_4 ## ## subjectid_5 ##
      A1        Raam        50                60                 70
      B1        Vivek       60                80                null
      c1        Alex       null              null                95

어떻게 SQLite는이 작업을 수행 할 수 있습니까?

해결법

  1. ==============================

    1.먼저 임시 테이블에 현재 테이블을 변경해야합니다 :

    먼저 임시 테이블에 현재 테이블을 변경해야합니다 :

    alter table student_info rename to student_name

    그런 다음 재 작성 student_info로 할 것입니다 :

    create table student_info add column (
    stuid VARCHAR(5) PRIMARY KEY,
    name VARCHAR(255),
    subjectid_3 INTEGER,
    subjectid_4 INTEGER,
    subjectid_5 INTEGER
)

    그런 다음, 채우기 student_info :

    insert into student_info
select
    u.stuid,
    u.name,
    s3.marks as subjectid_3,
    s4.marks as subjectid_4,
    s5.marks as subjectid_5
from
    student_temp u
    left outer join markdetails s3 on
        u.stuid = s3.stuid
        and s3.subjectid = 3
    left outer join markdetails s4 on
        u.stuid = s4.stuid
        and s4.subjectid = 4
    left outer join markdetails s5 on
        u.stuid = s5.stuid
        and s5.subjectid = 5

    이제, 당신의 임시 테이블을 삭제 :

    drop table student_temp

    그리고 당신이 신속하게 테이블을 업데이트 할 수있는 방법입니다.

    당신이 할 수있는 최선의 일부 왼쪽 조인 하드 코드 그래서 SQLite는은, 피벗 기능이 부족하다. A는 자신이 처음부터 모든 행에 대한 조건과는 null을 조인 행을 일치 가져올 것이다 가입 왼쪽, 또는 두 번째 테이블의 조인 조건을 만족하지 않는, 테이블을 떠났다.

  2. ==============================

    2.저자는 스키마를 생성하는 SQL을 줄만큼 친절하지 않았다 때문에, 여기가 @Eric에서 솔루션을 시도하고자하는 사람입니다.

    저자는 스키마를 생성하는 SQL을 줄만큼 친절하지 않았다 때문에, 여기가 @Eric에서 솔루션을 시도하고자하는 사람입니다.

    create table markdetails (studid, subjectid, marks);
create table student_info (studid, name);

insert into markdetails values('A1', 3, 50);
insert into markdetails values('A1', 4, 60);
insert into markdetails values('A1', 5, 70);
insert into markdetails values('B1', 3, 60);
insert into markdetails values('B1', 4, 80);
insert into markdetails values('C1', 5, 95);

insert into student_info values('A1', 'Raam');
insert into student_info values('B1', 'Vivek');
insert into student_info values('C1', 'Alex');

    여기에 의해 그룹의 경우를 사용하는 대안적인 해결책이다.

    select
    si.studid,
    si.name,
    sum(case when md.subjectid = 3 then md.marks end) subjectid_3,
    sum(case when md.subjectid = 4 then md.marks end) subjectid_4,
    sum(case when md.subjectid = 5 then md.marks end) subjectid_5
from student_info si
join markdetails md on
        md.studid = si.studid
group by si.studid, si.name
;

    비교를 위해, 여기에 에릭의 솔루션 @에서 같은 select 문은 다음과 같습니다

    select
    u.stuid,
    u.name,
    s3.marks as subjectid_3,
    s4.marks as subjectid_4,
    s5.marks as subjectid_5
from
    student_info u
    left outer join markdetails s3 on
        u.stuid = s3.stuid
        and s3.subjectid = 3
    left outer join markdetails s4 on
        u.stuid = s4.stuid
        and s4.subjectid = 4
    left outer join markdetails s5 on
        u.stuid = s5.stuid
        and s5.subjectid = 5
;

    많은 양의 데이터가있을 때 하나가 더 나은 실적을 것이다 보는 것은 흥미로운 일이 될 것이다.

  3. ==============================

    3.큰 부록! 낮은 노력과 시스템 부하와 유사한 문제를 해결하기 위해 저를 도왔다. I는 다음과 같이 1wire 인터페이스 DS18B20 온도 센서 데이터를 획득하기 위해 라즈베리 파이를 사용하고 :

    큰 부록! 낮은 노력과 시스템 부하와 유사한 문제를 해결하기 위해 저를 도왔다. I는 다음과 같이 1wire 인터페이스 DS18B20 온도 센서 데이터를 획득하기 위해 라즈베리 파이를 사용하고 :

    CREATE TABLE temps (Timestamp DATETIME, sensorID TEXT, temperature NUMERIC);

    예:

    sqlite> .headers on
sqlite> .mode column
sqlite> select * from temps where timestamp > '2014-02-24 22:00:00';

Timestamp            sensorID         temperature
-------------------  ---------------  -----------
2014-02-24 22:00:02  28-0000055f3f10  19.937
2014-02-24 22:00:03  28-0000055f0378  19.687
2014-02-24 22:00:04  28-0000055eb504  19.937
2014-02-24 22:00:05  28-0000055f92f2  19.937
2014-02-24 22:00:06  28-0000055eef29  19.812
2014-02-24 22:00:07  28-0000055f7619  19.625
2014-02-24 22:00:08  28-0000055edf01  19.687
2014-02-24 22:00:09  28-0000055effda  19.812
2014-02-24 22:00:09  28-0000055e5ef2  19.875
2014-02-24 22:00:10  28-0000055f1b83  19.812
2014-02-24 22:10:03  28-0000055f3f10  19.937
2014-02-24 22:10:04  28-0000055f0378  19.75
2014-02-24 22:10:04  28-0000055eb504  19.937
2014-02-24 22:10:05  28-0000055f92f2  19.937

    나는 10 개 기간을 "정상화"타임 스탬프 오전 SUBSTR () 명령을 사용하여. sensorID 가입하여 룩업 테이블 '센서'를 사용 SensorName로 변경

    CREATE VIEW [TempsSlot10min] AS
SELECT SUBSTR(datetime(timestamp),1,15)||'0:00' AS TimeSlot,
SensorName,
temperature FROM
temps JOIN sensors USING (sensorID, sensorID);

    예:

    sqlite> select * from TempsSlot10min where timeslot >= '2014-02-24 22:00:00';

TimeSlot             SensorName  temperature
-------------------  ----------  -----------
2014-02-24 22:00:00  T1          19.937
2014-02-24 22:00:00  T2          19.687
2014-02-24 22:00:00  T3          19.937
2014-02-24 22:00:00  T4          19.937
2014-02-24 22:00:00  T5          19.812
2014-02-24 22:00:00  T6          19.625
2014-02-24 22:00:00  T10         19.687
2014-02-24 22:00:00  T9          19.812
2014-02-24 22:00:00  T8          19.875
2014-02-24 22:00:00  T7          19.812
2014-02-24 22:10:00  T1          19.937
2014-02-24 22:10:00  T2          19.75
2014-02-24 22:10:00  T3          19.937
2014-02-24 22:10:00  T4          19.937
2014-02-24 22:10:00  T5          19.875

    이제 마법은 위에서 언급 한 CASE 명령으로 발생합니다.

    CREATE VIEW [PivotTemps10min] AS
SELECT TimeSlot,
AVG(CASE WHEN sensorName = 'T1' THEN temperature END) AS T1,
AVG(CASE WHEN sensorName = 'T2' THEN temperature END) AS T2,
...
AVG(CASE WHEN sensorName = 'T10' THEN temperature END) AS T10
FROM TempsSlot10min
GROUP BY TimeSlot;

    예:

    select * from PivotTemps10min where timeslot >= '2014-02-24 22:00:00';

TimeSlot             T1          T2              T10
-------------------  ----------  ---------- ...  ----------
2014-02-24 22:00:00  19.937      19.687          19.687
2014-02-24 22:10:00  19.937      19.75           19.687
2014-02-24 22:20:00  19.937      19.75           19.687
2014-02-24 22:30:00  20.125      19.937          19.937
2014-02-24 22:40:00  20.187      20.0            19.937
2014-02-24 22:50:00  20.25       20.062          20.062
2014-02-24 23:00:00  20.25       20.062          20.062

    여기 남은 유일한 문제는 sensorName '가 T1'... 'T10'은 이제 뷰에 하드 코딩되어있다 [PivotTemps10min]를 상기 룩업 테이블에서 촬영되지.

    그럼에도 불구하고,이 THEAD의 답변 대단히 감사합니다!

  4. ==============================

    4.같은 분야에서 아이들을 함께 묶는 간단한 요구 사항이있는 경우, GROUP_CONCAT은 당신의 친구입니다.

    같은 분야에서 아이들을 함께 묶는 간단한 요구 사항이있는 경우, GROUP_CONCAT은 당신의 친구입니다.

    이 스레드에서 사이먼 군침에 거대한 감사 : http://sqlite.1065341.n5.nabble.com/Howto-pivot-in-SQLite-tp26766p26771.html

  5. ==============================

    5.여기 @ pospec4444의 링크 덕분에 @ haridsv의 멋진 대답의 버전을 수정됩니다. 그것은 좀 더 간결 필터 절을 사용

    여기 @ pospec4444의 링크 덕분에 @ haridsv의 멋진 대답의 버전을 수정됩니다. 그것은 좀 더 간결 필터 절을 사용

    select
    si.studid,
    si.name,
    sum(md.marks) filter(where md.subjectid = 3) subjectid_3,
    sum(md.marks) filter(where md.subjectid = 4) subjectid_4,
    sum(md.marks) filter(where md.subjectid = 5) subjectid_5
from student_info si
join markdetails md on
        md.studid = si.studid
group by si.studid, si.name
;

    6. from https://stackoverflow.com/questions/1237068/how-to-pivot-in-sqlite-or-i-e-select-in-wide-format-a-table-stored-in-long-form by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] mysql_num_rows도 () : 제공된 인수가 유효한 MySQL의 결과 리소스 아니다 [중복]  (0) 2020.04.08
[SQL] SQL Server의 N 행을 선택  (0) 2020.04.08
[SQL] MySQL은 여러 열을 기준으로 순위를 결정  (0) 2020.04.08
[SQL] PostgreSQL을 가진 피벗 테이블 만들기  (0) 2020.04.08
[SQL] MySQL의 반복, 컬럼 이름을 통하여  (0) 2020.04.08
,

티스토리툴바