如何让这个巨人加入sqlite

Question

我正在使用Python3分析来自sqlite数据库文件的一些数据。我想用Python将所有表连接到一个巨大的表中。我知道一些python命令来实现这一点，但是sql语句太复杂了，我根本无法理解。我需要帮助创建我将在数据库文件上执行的sql语句。我还希望所有这些数据也能作为数据帧输出

在sqlite文件中，我有以下表格：

station
     id
     name
     lat
     long
     dock_count
     city
     installation_date
status
     station_id
     bikes_available
     docks_available
     time
trip
     id
     duration
     start_date
     start_station_name
     start_station_id
     end_date
     end_station_name
     end_station_id
     bike_id
     subscription_type
     zip_code
weather
     date
     max_temperature_f
     mean_temperature_f
     min_temperature_f
     max_dew_point_f
     mean_dew_point_f
     min_dew_point_f
     max_humidity
     mean_humidity
     min_humidity
     max_sea_level_pressure_inches
     mean_sea_level_pressure_inches
     min_sea_level_pressure_inches
     max_visibility_miles
     mean_visibility_miles
     min_visibility_miles
     max_wind_Speed_mph
     mean_wind_speed_mph
     max_gust_speed_mph
     precipitation_inches
     cloud_cover
     events
     wind_dir_degrees
     zip_code

我想把所有的表合并成一个巨大的表，然后用所有合并的数据选择1000次行程。这意味着我需要知道行程表中的一些外键，它们是：

start_date, points to weather, status

start_station_id, points to station

end_date, points to weather, status

end_station_id points to station

我想到的加入方式如下：

select 1000 rows from trip join (

weather where trip.start_date = weather.date as startweather

) and join (

weather where trip.end_date = weather.date as endweather

) and join (

station where trip.start_station_id = station.id as startstation

)  and join(

station where trip.end_station_id = station.id as endstation

) and join (

status where trip.start_station_id = station.status_id and trip.start_date = station.date as startstationstatus

) and join(

status where trip.end_station_id = station.status_id and trip.end_date = station.date as endstationstatus)

)

Answer 1

我将发布这个问题的答案，因为我最终得到的查询显示了sqlite的许多不同特性。以下是我使用的查询：

Select count() FROM trip as tr INNER JOIN station as startst on startst.id = tr.start_station_id INNER JOIN station as endst on endst.id = tr.end_station_id INNER JOIN weather as startwea on startwea.date = SUBSTR(tr.start_date,1,9) INNER JOIN status as ststat on trim(substr(ststat.time, 6, 2), "0") = substr(tr.start_date, 1, instr(tr.start_date,"/") - 1) and trim(substr(ststat.time, 9, 2), "0") = substr(tr.start_date, instr(tr.start_date,"/") + 1, instr(substr(tr.start_date, instr(tr.start_date,"/") + 1),"/") - 1) and substr(ststat.time, 1, 4) = substr(tr.start_date, instr(tr.start_date, " "), -4) WHERE tr.id  > 0 AND tr.id <= 7000000 AND tr.id % 100000 = 0

最后它太复杂了，因为我必须用一个日期列连接两个表，并且每个列中的日期格式不同