Подтвердить что ты не робот

Pivot в SQLite

Я хотел бы получить таблицу, в которой учащиеся и метки, которые они получают для всех своих субъектов, в одном запросе.

Это моя структура таблицы:

Таблица: markdetails

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

Таблица: student info

Фактическая структура:

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

Я хочу, чтобы результирующий набор выглядел следующим образом:

Таблица: student info

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

Как я могу выполнить это в SQLite?

4b9b3361

ОТВЕТЫ

Ответ 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 не имеет функции pivot, поэтому лучше всего вы можете сделать жесткие коды для некоторых левых объединений. A left join приведет к сопоставлению любых строк в своих условиях соединения и возвращает null для любых строк из первой или левой таблицы, которые не соответствуют условиям соединения для второй таблицы.

Ответ 2

Поскольку автор не был достаточно добр, чтобы дать 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');

Вот альтернативное решение, использующее case с group by.

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 из решения @Eric:

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
;

Будет интересно посмотреть, какой из них будет работать лучше, когда будет много данных.

Ответ 3

отличное приложение! помог мне решить подобную проблему с низким усилием и нагрузкой на систему. Я использую Raspberry Pi для получения данных датчика температуры DS18B20 с интерфейсом 1wire-интерфейса следующим образом:

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

используя команду SUBSTR(), я "нормализую" временные метки до 10 минут. С ПРИСОЕДИНЕНИЕМ 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' теперь жестко закодирован в VIEW [PivotTemps10min] и не берется из таблицы поиска.

Тем не менее, большое спасибо за ответы в этом thead!