diff options
| -rw-r--r-- | domagi/path-depth.sql | 9 | ||||
| -rw-r--r-- | domagi/schema.sql | 7 |
2 files changed, 7 insertions, 9 deletions
diff --git a/domagi/path-depth.sql b/domagi/path-depth.sql index 081d015..f4d33d3 100644 --- a/domagi/path-depth.sql +++ b/domagi/path-depth.sql @@ -9,15 +9,6 @@ WITH path_depth AS ( INNER JOIN path ON path.id=path_segment.path_id -- subset paths WHERE ($1 IS NULL) OR (path.name IN (SELECT UNNEST($1))) - GROUP BY path_id), - path_length AS ( - -- Compute length of each path. - SELECT path_id, sum(len(sequence)) AS length - FROM path_segment - INNER JOIN segment ON segment_id=segment.id - INNER JOIN path ON path.id=path_segment.path_id - -- subset paths (same filter as above) - WHERE (($1 IS NULL) OR (path.name IN (SELECT UNNEST($1)))) GROUP BY path_id) -- Combine path name, length and mean depth for display. SELECT name, length, mean_depth diff --git a/domagi/schema.sql b/domagi/schema.sql index 29066f9..f9a867f 100644 --- a/domagi/schema.sql +++ b/domagi/schema.sql @@ -28,6 +28,13 @@ CREATE TABLE path_segment ( "end" INTEGER, ); +-- One-to-one relation mapping path IDs to their length +CREATE VIEW path_length AS + SELECT ANY_VALUE(path_id) AS path_id, sum(len(sequence))::INTEGER AS length + FROM path_segment + INNER JOIN segment ON segment.id=path_segment.segment_id + GROUP BY path_id; + -- One-to-one relation mapping segment IDs to their depth CREATE VIEW segment_depth AS WITH segment_depth_nonzero_depths_only AS ( |