在Tableau中基于多数据源时间区间过滤时序数据

Got it, let's tackle this problem step by step—you're trying to filter rows from Source 1 based on the time intervals defined by Source 2's start/stop events in Tableau, right? Here are two reliable methods using Tableau Prep and Tableau Desktop that should get you the exact output you need:

Tableau Prep is perfect for this kind of ETL-style filtering since it lets you shape and join data easily:

1. Import both data sources
   Drag your Source 1 (numeric data) and Source 2 (event timestamps) into the Prep canvas.
2. Transform Source 2 into time intervals
   • First, add a Calculated Field to Source 2 named Interval Group with this formula:

     RUNNING_SUM(IF [event] = 'start' THEN 1 ELSE 0 END)
     

     This groups each start and its corresponding stop into the same interval ID.
   • Next, use the Aggregate tool on Source 2:
     • Group by Interval Group
     • Create two aggregated fields:
       • start_time: MIN(IF [event] = 'start' THEN [timestamp] END)
       • end_time: MIN(IF [event] = 'stop' THEN [timestamp] END)
         You’ll now have a clean table of valid time intervals.
3. Join and filter
   • Drag the transformed Source 2 (interval table) onto Source 1 to create a Cross Join (select Cross Join from the join type dropdown).
   • Add a Filter tool, set the condition to:

     [Source 1.timestamp] >= [start_time] AND [Source 1.timestamp] <= [end_time]
     

4. Clean up and export
   Remove any extra fields (like Interval Group, start_time, end_time) if you don’t need them, then output the filtered Source 1 data—this will match your desired result exactly.

If you need to do this directly in Desktop while building a view, here’s how:

1. Connect to both data sources
   Add Source 1 and Source 2 to your Desktop data source. If they’re separate files (like CSVs), they’ll show up as two tables in the Data Source tab.
2. Generate intervals with Custom SQL
   Right-click Source 2 in the Data Source tab, select Convert to Custom SQL, and replace the default query with this:

   WITH ranked_events AS (
       SELECT 
           event, 
           timestamp,
           ROW_NUMBER() OVER (ORDER BY timestamp) AS row_num
       FROM [Source 2]
   )
   SELECT 
       r1.timestamp AS start_time,
       r2.timestamp AS end_time
   FROM ranked_events r1
   JOIN ranked_events r2 ON r1.row_num = r2.row_num - 1
   WHERE r1.event = 'start' AND r2.event = 'stop'
   

   This query pairs each start with the next stop to create valid intervals.
3. Join and filter
   • Drag the custom SQL interval table onto Source 1 to create a Cross Join.
   • Create a Calculated Field named In Valid Interval? with:

     [Source 1.timestamp] >= [start_time] AND [Source 1.timestamp] <= [end_time]
     

   • Drag In Valid Interval? to the Filters pane and select True.
4. View the results
   Drag Source 1’s x, y, z, and timestamp fields into your view—you’ll only see rows that fall within the intervals from Source 2.

Quick Notes #

• Make sure both timestamp fields are the same data type (in your case, they’re both Unix timestamps/integers, so this is already handled).
• If your Source 2 ever has non-alternating events (e.g., two starts in a row), you’ll need to adjust the grouping logic, but your sample data is perfectly structured for these methods.

内容的提问来源于stack exchange，提问作者7ball