Início / dba / Perguntas / 239709
Accepted
arilwan
Asked: 2019-06-04 07:45:08 +0800 CST

Alterar os valores em uma coluna

  • 772

Tenho em mãos a seguinte tabela (para migração de dados):

recvtimets     |         recvtime         | fiwareservicepath | entityid |   entitytype    |     attrname     |    attrtype     |               attrvalue               | attrmd
---------------+--------------------------+-------------------+----------+-----------------+------------------+-----------------+---------------------------------------+--------
 1559573267088 | 2019-06-03T14:47:47.88Z  | /basic            | 001      | WeatherObserved | location         | StructuredValue | {"coordinates":[-8.613502,41.143994]} | []
 1559573267088 | 2019-06-03T14:47:47.88Z  | /basic            | 001      | WeatherObserved | relativeHumidity | Number          | 59.1                                  | []
 1559573267088 | 2019-06-03T14:47:47.88Z  | /basic            | 001      | WeatherObserved | dateObserved     | Text            | 2015-09-01T10:15:57+01:00             | []
 1559573267088 | 2019-06-03T14:47:47.88Z  | /basic            | 001      | WeatherObserved | illuminance      | None            | null                                  | []
 1559573267088 | 2019-06-03T14:47:47.88Z  | /basic            | 001      | WeatherObserved | temperature      | Number          | 24.9                                  | []
 1559573267088 | 2019-06-03T14:47:47.88Z  | /basic            | 001      | WeatherObserved | name             | Text            | Rua das Flores                        | []
 1559573267782 | 2019-06-03T14:47:47.782Z | /basic            | 001      | WeatherObserved | name             | Text            | Praca do Marques - pole 1             | []
 1559573267782 | 2019-06-03T14:47:47.782Z | /basic            | 001      | WeatherObserved | illuminance      | None            | null                                  | []
 1559573267782 | 2019-06-03T14:47:47.782Z | /basic            | 001      | WeatherObserved | temperature      | Number          | 21.9                                  | []
 1559573267782 | 2019-06-03T14:47:47.782Z | /basic            | 001      | WeatherObserved | dateObserved     | Text            | 2015-09-01T10:16:24+01:00             | []
 1559573267782 | 2019-06-03T14:47:47.782Z | /basic            | 001      | WeatherObserved | location         | StructuredValue | {"coordinates":[-8.603802,41.161671]} | []
 1559573267782 | 2019-06-03T14:47:47.782Z | /basic            | 001      | WeatherObserved | relativeHumidity | Number          | 80.8                                  | []
 1559573267696 | 2019-06-03T14:47:47.696Z | /basic            | 001      | WeatherObserved | name             | Text            | Fundacao de Serralves                 | []
 1559573267696 | 2019-06-03T14:47:47.696Z | /basic            | 001      | WeatherObserved | illuminance      | None            | null                                  | []
 1559573267696 | 2019-06-03T14:47:47.696Z | /basic            | 001      | WeatherObserved | location         | StructuredValue | {"coordinates":[-8.65915,41.1591]}    | []
 1559573267696 | 2019-06-03T14:47:47.696Z | /basic            | 001      | WeatherObserved | relativeHumidity | Number          | 1                                     | []
 1559573267696 | 2019-06-03T14:47:47.696Z | /basic            | 001      | WeatherObserved | dateObserved     | Text            | 2015-09-01T10:16:22+01:00             | []

As duas primeiras colunas descrevem o carimbo de data/hora que o aplicativo grava no banco de dados (leitura de algum banco de dados). O tempo para o qual os dados foram realmente registrados, no entanto, é o dateObservedatributo na attrnamecoluna e seu valor correspondente é o mostrado na attrvaluecoluna.

Isso significa que as primeiras 6 linhas (tendo 2019-06-03T14:47:47.88Zna recvtimecoluna foram registradas em 2015-09-01T10:15:57+01:00(mostradas na attrvaluecoluna), ou seja,dateObserved = 2015-09-01T10:15:57+01:00

As próximas 6 linhas 2019-06-03T14:47:47.782Zna recvtimecoluna foram registradas em 2015-09-01T10:16:24+01:00, ou seja,dateObserved =2015-09-01T10:16:24+01:00

Da mesma forma, as últimas 6 linhas com recvtimeas 2019-06-03T14:47:47.696Zforam registradas em 2015-09-01T10:16:22+01:00iedateObserved = 2015-09-01T10:16:22+01:00

O objetivo é alterar o conteúdo da recvtimecoluna de forma que esses valores sejam substituídos pela data registrada conforme contida no attrvaluepara as respectivas linhas.

Assim, o resultado final fica como na tabela a seguir (ou seja, a tabela final para produzir isso):

recvtimets     |           recvtime         | fiwareservicepath | entityid |   entitytype    |     attrname     |    attrtype     |               attrvalue               | attrmd
---------------+----------------------------+-------------------+----------+-----------------+------------------+-----------------+---------------------------------------+--------
 1559573267088 | 2015-09-01T10:15:57+01:00  | /basic            | 001      | WeatherObserved | location         | StructuredValue | {"coordinates":[-8.613502,41.143994]} | []
 1559573267088 | 2015-09-01T10:15:57+01:00  | /basic            | 001      | WeatherObserved | relativeHumidity | Number          | 59.1                                  | []
 1559573267088 | 2015-09-01T10:15:57+01:00  | /basic            | 001      | WeatherObserved | dateObserved     | Text            | 2015-09-01T10:15:57+01:00             | []
 1559573267088 | 2015-09-01T10:15:57+01:00  | /basic            | 001      | WeatherObserved | illuminance      | None            | null                                  | []
 1559573267088 | 2015-09-01T10:15:57+01:00  | /basic            | 001      | WeatherObserved | temperature      | Number          | 24.9                                  | []
 1559573267088 | 2015-09-01T10:15:57+01:00  | /basic            | 001      | WeatherObserved | name             | Text            | Rua das Flores                        | []
 1559573267782 | 2015-09-01T10:16:24+01:00  | /basic            | 001      | WeatherObserved | name             | Text            | Praca do Marques - pole 1             | []
 1559573267782 | 2015-09-01T10:16:24+01:00  | /basic            | 001      | WeatherObserved | illuminance      | None            | null                                  | []
 1559573267782 | 2015-09-01T10:16:24+01:00  | /basic            | 001      | WeatherObserved | temperature      | Number          | 21.9                                  | []
 1559573267782 | 2015-09-01T10:16:24+01:00  | /basic            | 001      | WeatherObserved | dateObserved     | Text            | 2015-09-01T10:16:24+01:00             | []
 1559573267782 | 2015-09-01T10:16:24+01:00  | /basic            | 001      | WeatherObserved | location         | StructuredValue | {"coordinates":[-8.603802,41.161671]} | []
 1559573267782 | 2015-09-01T10:16:24+01:00  | /basic            | 001      | WeatherObserved | relativeHumidity | Number          | 80.8                                  | []
 1559573267696 | 2015-09-01T10:16:22+01:00  | /basic            | 001      | WeatherObserved | name             | Text            | Fundacao de Serralves                 | []
 1559573267696 | 2015-09-01T10:16:22+01:00  | /basic            | 001      | WeatherObserved | illuminance      | None            | null                                  | []
 1559573267696 | 2015-09-01T10:16:22+01:00  | /basic            | 001      | WeatherObserved | location         | StructuredValue | {"coordinates":[-8.65915,41.1591]}    | []
 1559573267696 | 2015-09-01T10:16:22+01:00  | /basic            | 001      | WeatherObserved | relativeHumidity | Number          | 1                                     | []
 1559573267696 | 2015-09-01T10:16:22+01:00  | /basic            | 001      | WeatherObserved | dateObserved     | Text            | 2015-09-01T10:16:22+01:00             | []

Como faço para conseguir isso no SQL?

postgresql

1 respostas

  1. Best Answer
    SELECT t1.recvtimets, 
       t2.attrvalue recvtime,
       t1.fiwareservicepath,
       t1.entityid,
       t1.entitytype,
       t1.attrname,
       t1.attrtype,
       t1.attrvalue,
       t1.attrmd
FROM tablename t1
JOIN tablename t2 ON t1.recvtimets = t2.recvtimets
                 AND t2.attrname = 'dateObserved'
WHERE t1.attrname != 'dateObserved'
    • 2

relate perguntas