Analyzing Concepts with SQL

1
-- Step 1: Identify occurrences of the specific concept in video keyframes
2
WITH occurrences AS (
3
  SELECT
4
    coactive_video_id,              -- Video ID
5
    keyframe_time_ms,               -- Timestamp in milliseconds
6
    baton_prob,                     -- Probability score for the baton concept
7
    MAX(coactive_image_id) AS coactive_image_id  -- Select the best image for representation
8
  FROM
9
    coactive_table_adv
10
  WHERE
11
    baton_prob > 0.5  -- Only include keyframes with a confidence score above 0.5
12
  GROUP BY
13
    coactive_video_id, keyframe_time_ms, baton_prob
14
)
15
16
-- Step 2: Retrieve and order results for concept occurrences
17
SELECT
18
  o.coactive_video_id,              -- Video ID
19
  o.keyframe_time_ms,               -- Time of occurrence
20
  o.baton_prob,                     -- Concept probability
21
  o.coactive_image_id               -- Best image for this occurrence
22
FROM
23
  occurrences o
24
ORDER BY
25
  o.coactive_video_id, o.keyframe_time_ms ASC;

1
-- Step 1: Filter images with the specified concept and sufficient confidence
2
WITH filtered_images AS (
3
  SELECT
4
    coactive_image_id,      -- Unique identifier for the image
5
    baton_prob AS baton_prob -- Confidence score for the concept
6
  FROM
7
    coactive_table_adv
8
  WHERE
9
    baton_prob > 0.1 -- Include only images with a probability above 0.1
10
)
11
12
-- Step 2: Retrieve the filtered images with their concept and certainty
13
SELECT
14
  coactive_image_id, -- Image ID
15
  baton_prob          -- Confidence score for the detected concept
16
FROM
17
  filtered_images
18
ORDER BY
19
  baton_prob DESC; -- Order by confidence score in descending order

1
-- Step 1: Aggregate occurrences of the concept across videos
2
WITH occurrences AS (
3
  SELECT
4
    coactive_video_id,
5
    COUNT(*) AS occurrence_count,  -- Count the number of frames with the concept
6
    MAX(coactive_image_id) AS coactive_image_id, -- Select a representative image for the video
7
    MAX(baton_prob) AS max_probability, -- Highest probability for the concept in the video
8
    MIN(baton_prob) AS min_probability -- Lowest probability for the concept in the video
9
  FROM
10
    coactive_table_adv
11
  WHERE
12
    baton_prob > 0.1 -- Only include frames with a probability above this threshold
13
  GROUP BY
14
    coactive_video_id -- Group by video to analyze concept occurrences within each
15
)
16
17
-- Step 2: Select relevant metadata for each video
18
SELECT
19
  o.max_probability,   -- Maximum probability for the concept
20
  o.min_probability,   -- Minimum probability for the concept
21
  o.occurrence_count,  -- Number of frames with the concept
22
  o.coactive_video_id, -- Video ID
23
  o.coactive_image_id  -- Best representative image ID for the video
24
FROM
25
  occurrences o
26
ORDER BY
27
  o.occurrence_count DESC; -- Order by the number of frames with the concept