PostgreSQL, R: умножить все строки таблицы для создания данных панели (временных рядов)

По сути, набор панельных данных представляет собой данные в формате long с повторяющимися годами для каждой записи в качестве столбца времени. Ваша текущая структура имеет формат широкий. В то время как R может преобразовывать этот очень большой набор данных, PostGreSQL может объединять все годы в запросе на объединение с помощью своего движка и передавать набор результатов в R. Обратите внимание, что некоторые типы данных, такие как объекты геометрии и массивы, могут неправильно преобразовываться в типы данных R, поэтому удалите их или преобразуйте в строковые/числовые типы.

Ниже приведен такой SQL-запрос UNION со сложенными годами. Я не совсем уверен, что вы имеете в виду под ibase_id и ibase_dist или аспектом «умножения», но столбец Year добавляется с соответствующим столбцом b. Пусть сценарий R вызывает его через модуль RPostGreSQL.

import("RPostgreSQL")

# CREATE CONNECTION     
drv <- dbDriver("PostgreSQL")
con <- dbConnect(drv, dbname = "postgres",
                 host = "localhost", port = ####,
                 user = "username", password = "password")

strSQL <- "SELECT '2000' As year,  gid, gembez, gemname, krsbez,
                 krsname, pv, gr, capac, dist, gemewz, n500
                 popden, instp, b2000 As b, (1/ibase_dist) As ibase
           FROM public.buildings
           INNER JOIN
                (SELECT a.gid AS buildid, 
                        SUM(round(ST_Distance(
                                              ST_Transform(a.centroid, 31467),  
                                              ST_Transform(b.centroid, 31467)
                                  )::integer)) AS ibase_dist
               FROM buildings a
               LEFT JOIN buildings b 
                      ON ST_DWithin(ST_Transform(a.centroid, 31467), 
                                    ST_Transform(b.centroid, 31467), 500.0)
                    AND a.gid <> b.gid
               WHERE b.pv=True AND b.instp < a.instp
               GROUP BY a.gid) AS distSum
           ON public.buildings.gid = distSum.buildid
           WHERE public.buildings.instp = 2000

           UNION

           ...other SELECT statements for years 2001-2010..."              

# IMPORT QUERY RESULTSET INTO DATAFRAME
df <- dbGetQuery(con, strSQL)

# CLOSE CONNECTION
dbDisconnect(con)

Но убедитесь, что у вас есть необходимая RAM для работы с большим набором данных. Возможно, вам потребуется выделить память соответствующим образом. Кроме того, вы можете итеративно добавлять оператор SELECT каждого года в растущий объект фрейма данных вместо того, чтобы загружать все сразу.

# ...SAME CONNECTION SETUP AS ABOVE...

years = c('2000', '2001', '2002', '2003', '2004', '2005', 
          '2006', '2007', '2008', '2009', '2010')

# CREATES LIST OF YEAR DATA FRAME
dfList = lapply(years, 
                function(y) {
                # NOTICE CONCATENATION OF Y IN SELECT STATEMENT 
                strSQL <- paste0("SELECT '", y, "' As year,  gid, gembez, gemname, krsbez,
                                         krsname, pv, gr, capac, dist, gemewz, n500, 
                                         popden, instp, b", y, ", As b, (1/ibase_dist) As ibase, 
                                  FROM public.buildings
                                  INNER JOIN
                                    (SELECT a.gid AS buildid, 
                                          SUM(round(ST_Distance(
                                              ST_Transform(a.centroid, 31467),  
                                              ST_Transform(b.centroid, 31467)
                                          )::integer)) AS ibase_dist
                                     FROM buildings a
                                     LEFT JOIN buildings b 
                                     ON ST_DWithin(ST_Transform(a.centroid, 31467), 
                                                   ST_Transform(b.centroid, 31467), 500.0)
                                     AND a.gid <> b.gid
                                     WHERE b.pv=True AND b.instp < a.instp
                                     GROUP BY a.gid) AS distSum
                                  ON public.buildings.gid = distSum.buildid
                                  WHERE public.buildings.instp =", y)
                dbGetQuery(con, strSQL)                               
                })

# APPEND LIST OF DATA FRAMES INTO ONE LARGE DATA FRAME              
df <- do.call(rbind, dfList)

# REMOVE PREVIOUS LIST FOR MEMORY RESOURCES
rm(dfList)

# CLOSE CONNECTION
dbDisconnect(con)

Я создал таблицу Paneldata с помощью перекрестного соединения с временной таблицей t1, содержащей периоды.

CREATE TABLE public.t1
(
  period smallint
)
WITH (
  OIDS=FALSE
);



CREATE TABLE paneldata AS
(SELECT * 
FROM t1 CROSS JOIN 
    (SELECT gid, 
    gemname, 
    gembez, 
    krsname,
    krsbez,
    pv,
    gr,
    capac,
    dist,
    gemewz,
    n500,
    popden,
    instp
    FROM buildings) AS test
ORDER BY gid)