构建AI前的数据准备，SQL要比Python强

# PYTHON
# connect to db using wrapper around psycopg2
db = DatabaseConnection(db='db', user='username', password='password')# grab data from db and load into memory
df = db.run_query("SELECT * FROM cleaned_table;")
df = pd.DataFrame(df, columns=['user_id', 'series_id', 'timestamp'])# calculate time since first visit
df = df.assign(time_since_first=df.groupby('user_id', sort=False).timestamp.apply(lambda x: x - x.min()))# calculate time since last visit
df = df.assign(time_since_last=df.sort_values(['timestamp'], ascending=True).groupby('user_id', sort=False)['timestamp'].transform(pd.Series.diff))# save df to compressed csv
df.to_csv('transform_time_test.gz', compression='gzip')

-- SQL equivalent
-- increase the working memory (be careful with this)
set work_mem='600MB';-- create a dual index on the partition
CREATE INDEX IF NOT EXISTS user_time_index ON table(user_id, timestamp);-- calculate time since last visit and time since first visit in one pass 
SELECT *, AGE(timestamp, LAG(timestamp, 1, timestamp) OVER w) AS time_since_last, AGE(timestamp, FIRST_VALUE(timestamp) OVER w) AS time_since_first FROM table WINDOW w AS (PARTITION BY user_id ORDER BY timestamp);

-- SQL (the -> syntax is how you parse json)
SELECT user_json->'info'->>'name' as user_name FROM user_table;

-- SQL
create or replace function is_json(text)
returns boolean language plpgsql immutable as $$
begin
    perform $1::json;
    return true;
exception
    when invalid_text_representation then 
        return false;
end $$;SELECT user_json->'info'->>'name' as user_name FROM user_table WHERE is_json(user_json);

-- SQL
create or replace function create_jsonb_flat_view
    (table_name text, regular_columns text, json_column text)
    returns text language plpgsql as $$
declare
    cols text;
begin
    execute format ($ex$
        select string_agg(format('%2$s->>%%1$L "%%1$s"', key), ', ')
        from (
            select distinct key
            from %1$s, jsonb_each(%2$s)
            order by 1
            ) s;
        $ex$, table_name, json_column)
    into cols;
    execute format($ex$
        drop view if exists %1$s_view;
        create view %1$s_view as 
        select %2$s, %3$s from %1$s
        $ex$, table_name, regular_columns, cols);
    return cols;
end $$;