docs: add page for common TP queries
Change-Id: I08f3e971754ba75fe32f2c139008be3b2f63fcb7
diff --git a/docs/analysis/common-queries.md b/docs/analysis/common-queries.md
new file mode 100644
index 0000000..98e8c1f
--- /dev/null
+++ b/docs/analysis/common-queries.md
@@ -0,0 +1,59 @@
+# Common queries
+
+This page acts as a reference guide for queries which often appear when
+performing ad-hoc analysis.
+
+## Computing CPU time for slices
+If collecting traces which including scheduling information (i.e. from ftrace)
+as well as userspace slices (i.e. from atrace), the actual time spent running
+on a CPU for each userspace slice can be computed: this is commonly known as
+the "CPU time" for a slice.
+
+Firstly, setup the views to simplify subsequent queries:
+```
+DROP VIEW IF EXISTS slice_with_utid;
+CREATE VIEW slice_with_utid AS
+SELECT
+ ts,
+ dur,
+ slice.name as slice_name,
+ slice.id as slice_id, utid,
+ thread.name as thread_name
+FROM slice
+JOIN thread_track ON thread_track.id = slice.track_id
+JOIN thread USING (utid);
+
+DROP TABLE IF EXISTS slice_thread_state_breakdown;
+CREATE VIRTUAL TABLE slice_thread_state_breakdown
+USING SPAN_LEFT_JOIN(
+ slice_with_utid PARTITIONED utid,
+ thread_state PARTITIONED utid
+);
+```
+
+Then, to compute the CPU time for all slices in the trace:
+```
+SELECT slice_id, slice_name, SUM(dur) AS cpu_time
+FROM slice_thread_state_breakdown
+WHERE state = 'Running'
+GROUP BY slice_id;
+```
+
+You can also compute CPU time for a specific slice:
+```
+SELECT slice_name, SUM(dur) AS cpu_time
+FROM slice_thread_state_breakdown
+WHERE slice_id = <your slice id> AND state = 'Running';
+```
+
+These queries can be varied easily to compute other similar metrics.
+For example to get the time spent "runnable" and in "uninterruptible sleep":
+```
+SELECT
+ slice_id,
+ slice_name,
+ SUM(CASE state = 'R' THEN dur ELSE 0 END) AS runnable_time,
+ SUM(CASE state = 'D' THEN dur ELSE 0 END) AS uninterruptible_time
+FROM slice_thread_state_breakdown
+GROUP BY slice_id;
+```
diff --git a/docs/toc.md b/docs/toc.md
index 0142029..be3b621 100644
--- a/docs/toc.md
+++ b/docs/toc.md
@@ -36,6 +36,7 @@
* [Trace analysis](#)
* [Trace Processor (SQL)](analysis/trace-processor.md)
* [Trace-based metrics](analysis/metrics.md)
+ * [Common queries](analysis/common-queries.md)
* [SQL tables](analysis/sql-tables.autogen)
* [Stats table](analysis/sql-stats.autogen)
