1name: lukekim.smart.drive_stats_with_local
2type: view
3migrations:
4  - name: create_view
5    sql: |
6      CREATE OR REPLACE VIEW lukekim.smart.drive_stats_with_local AS (
7        with aggregated_data as (
8          SELECT 
9            Cast(time_unix_nano / 1e6 AS BIGINT) AS ts, 
10            model, 
11            NAME, 
12            serial_no, 
13            id, 
14            "value" 
15          FROM 
16            lukekim.smart.drive_stats
17        ), 
18        uploaded_data as (
19          SELECT 
20            t.ts, 
21            t.model, 
22            t.serial_no as serial_number, 
23            cast(
24              max(
25                CASE WHEN t.id = 194 THEN t."value" END
26              ) AS DOUBLE
27            ) AS smart_194, 
28            cast(
29              max(
30                CASE WHEN t.id = 12 THEN t."value" END
31              ) AS DOUBLE
32            ) AS smart_12, 
33            cast(
34              max(CASE WHEN t.id = 9 THEN t."value" END) AS DOUBLE
35            ) AS smart_9, 
36            cast(0.0 AS DOUBLE) AS y 
37          FROM 
38            aggregated_data t 
39          GROUP BY 
40            t.ts, 
41            t.model, 
42            t.serial_no 
43          ORDER BY 
44            t.ts DESC
45        ),
46        daily_data as (
47        SELECT 
48          (ts / 3600000 / 24 * 24 * 3600000) as ts,
49          max(y) as life_percent, 
50          max(smart_9) as smart_9_raw, 
51          max(smart_12) as smart_12_raw, 
52          max(smart_194) as smart_194_raw, 
53          serial_number 
54        FROM 
55          uploaded_data
56        group by ts, serial_number
57        )
58        SELECT 
59          ts,
60          life_percent, 
61          smart_9_raw, 
62          smart_12_raw, 
63          smart_194_raw, 
64          serial_number 
65        FROM 
66          daily_data
67        union
68        SELECT 
69          ts, 
70          life_percent, 
71          smart_9_raw, 
72          smart_12_raw, 
73          smart_194_raw, 
74          serial_number 
75        FROM 
76          spiceai.datasets.smart_failures
77      )
78

1name: lukekim.smart.drive_stats_with_local
2type: view
3migrations:
4  - name: create_view
5    sql: |
6      CREATE OR REPLACE VIEW lukekim.smart.drive_stats_with_local AS (
7        with aggregated_data as (
8          SELECT 
9            Cast(time_unix_nano / 1e6 AS BIGINT) AS ts, 
10            model, 
11            NAME, 
12            serial_no, 
13            id, 
14            "value" 
15          FROM 
16            lukekim.smart.drive_stats
17        ), 
18        uploaded_data as (
19          SELECT 
20            t.ts, 
21            t.model, 
22            t.serial_no as serial_number, 
23            cast(
24              max(
25                CASE WHEN t.id = 194 THEN t."value" END
26              ) AS DOUBLE
27            ) AS smart_194, 
28            cast(
29              max(
30                CASE WHEN t.id = 12 THEN t."value" END
31              ) AS DOUBLE
32            ) AS smart_12, 
33            cast(
34              max(CASE WHEN t.id = 9 THEN t."value" END) AS DOUBLE
35            ) AS smart_9, 
36            cast(0.0 AS DOUBLE) AS y 
37          FROM 
38            aggregated_data t 
39          GROUP BY 
40            t.ts, 
41            t.model, 
42            t.serial_no 
43          ORDER BY 
44            t.ts DESC
45        ),
46        daily_data as (
47        SELECT 
48          (ts / 3600000 / 24 * 24 * 3600000) as ts,
49          max(y) as life_percent, 
50          max(smart_9) as smart_9_raw, 
51          max(smart_12) as smart_12_raw, 
52          max(smart_194) as smart_194_raw, 
53          serial_number 
54        FROM 
55          uploaded_data
56        group by ts, serial_number
57        )
58        SELECT 
59          ts,
60          life_percent, 
61          smart_9_raw, 
62          smart_12_raw, 
63          smart_194_raw, 
64          serial_number 
65        FROM 
66          daily_data
67        union
68        SELECT 
69          ts, 
70          life_percent, 
71          smart_9_raw, 
72          smart_12_raw, 
73          smart_194_raw, 
74          serial_number 
75        FROM 
76          spiceai.datasets.smart_failures
77      )
78