db_test_lab/sql_strings.py at master · syrok0010/db_test_lab · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
from config_loader import table_name

query_sql = [
    f'SELECT "VendorID", count(*) FROM {table_name} GROUP BY 1;',
    f'SELECT passenger_count, avg(total_amount) FROM {table_name} GROUP BY 1;',
    f'''
        SELECT
            passenger_count,
            date_part('Year', tpep_pickup_datetime::date),
            count(*)
        FROM {table_name}
        GROUP BY 1, 2;
    ''',
    f'''
        SELECT
            passenger_count,
            date_part('Year', tpep_pickup_datetime::date),
            round(trip_distance),
            count(*)
        FROM {table_name}
        GROUP BY 1, 2, 3
        ORDER BY 2, 4 desc;
    '''
]

query_sql_sqlite = [
    f'SELECT "VendorID", count(*) FROM {table_name} GROUP BY 1;',
    f'SELECT passenger_count, avg(total_amount) FROM {table_name} GROUP BY 1;',
    f'''
        SELECT
            passenger_count,
            strftime('%Y', tpep_pickup_datetime) AS "Year",
            count(*)
        FROM {table_name}
        GROUP BY 1, 2;
    ''',
    f'''
        SELECT
            passenger_count,
            strftime('%Y', tpep_pickup_datetime) AS "Year",
            round(trip_distance),
            count(*)
        FROM {table_name}
        GROUP BY 1, 2, 3
        ORDER BY 2, 4 desc;
    '''
]

create_table_sql = f'''
    create table if not exists {table_name}
    (
        "Id"                  bigint,
        "VendorID"            bigint,
        tpep_pickup_datetime  text,
        tpep_dropoff_datetime text,
        passenger_count       double precision,
        trip_distance         double precision,
        "RatecodeID"          double precision,
        store_and_fwd_flag    text,
        "PULocationID"        bigint,
        "DOLocationID"        bigint,
        payment_type          bigint,
        fare_amount           double precision,
        extra                 double precision,
        mta_tax               double precision,
        tip_amount            double precision,
        tolls_amount          double precision,
        improvement_surcharge double precision,
        total_amount          double precision,
        congestion_surcharge  double precision,
        airport_fee           double precision
    );
'''


import_csv_sql = f'''
    COPY {table_name}("Id", "VendorID", tpep_pickup_datetime, tpep_dropoff_datetime, passenger_count, trip_distance, "RatecodeID",
              store_and_fwd_flag, "PULocationID", "DOLocationID", payment_type, fare_amount, extra, mta_tax, tip_amount,
              tolls_amount, improvement_surcharge, total_amount, congestion_surcharge, airport_fee)
    FROM STDIN
    DELIMITER ','
    CSV HEADER;
'''


drop_table_sql = f'DROP TABLE if exists {table_name}'