inference.ts 9.7 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263
  1. import { Resource } from "sst/resource"
  2. import type { AthenaData } from "../athena"
  3. import type { GeoStatAggregate } from "./geo"
  4. import type { ModelStatAggregate } from "./model"
  5. import {
  6. EXCLUDED_MODELS,
  7. MODEL_AUTHOR_RULES,
  8. RETIRED_STAT_PROVIDERS,
  9. statModel,
  10. statProvider,
  11. } from "./model-normalization"
  12. import type { ProviderStatAggregate } from "./provider"
  13. import { normalizeCountry, normalizeTier, type StatBaseAggregate } from "./stat"
  14. export type StatDimension = "model" | "provider" | "geo" | "geo_model"
  15. export function buildStatsQuery(periodStart: Date, periodEnd: Date, dimension: StatDimension) {
  16. const periodStartValue = sqlString(periodStart.toISOString())
  17. const periodEndValue = sqlString(periodEnd.toISOString())
  18. const sourceTable = [Resource.InferenceEvent.catalog, Resource.InferenceEvent.database, Resource.InferenceEvent.table]
  19. .map(sqlIdentifier)
  20. .join(".")
  21. const dimensionSql = (() => {
  22. if (dimension === "model")
  23. return {
  24. select: "provider, model, COALESCE(MAX(NULLIF(provider_model, '')), '') AS provider_model",
  25. groupBy: "provider, model",
  26. }
  27. if (dimension === "provider") return { select: "provider", groupBy: "provider" }
  28. if (dimension === "geo_model")
  29. return {
  30. select: "provider, model, country, COALESCE(MAX(NULLIF(continent, '')), '') AS continent",
  31. groupBy: "provider, model, country",
  32. }
  33. return {
  34. select: "'all' AS provider, 'all' AS model, country, COALESCE(MAX(NULLIF(continent, '')), '') AS continent",
  35. groupBy: "country",
  36. }
  37. })()
  38. const aggregateColumns = `
  39. COUNT(DISTINCT session) AS sessions,
  40. COUNT(*) AS requests,
  41. COALESCE(SUM(tokens_input), 0) AS input_tokens,
  42. COALESCE(SUM(tokens_output), 0) AS output_tokens,
  43. COALESCE(SUM(tokens_reasoning), 0) AS reasoning_tokens,
  44. COALESCE(SUM(tokens_cache_read), 0) AS cache_read_tokens,
  45. COALESCE(SUM(tokens_total), 0) AS total_tokens,
  46. COALESCE(SUM(cost_input_microcents), 0) AS input_cost_microcents,
  47. COALESCE(SUM(cost_output_microcents), 0) AS output_cost_microcents,
  48. COALESCE(SUM(cost_total_microcents), 0) AS total_cost_microcents,
  49. AVG(duration_ms) AS avg_duration_ms,
  50. approx_percentile(CAST(duration_ms AS double), 0.5) AS p50_duration_ms,
  51. approx_percentile(CAST(duration_ms AS double), 0.95) AS p95_duration_ms,
  52. AVG(ttfb_ms) AS avg_ttfb_ms,
  53. approx_percentile(CAST(ttfb_ms AS double), 0.5) AS p50_ttfb_ms,
  54. approx_percentile(CAST(ttfb_ms AS double), 0.95) AS p95_ttfb_ms,
  55. AVG(output_tps) AS avg_output_tps,
  56. SUM(CASE WHEN status >= 200 AND status < 400 THEN 1 ELSE 0 END) AS success_count,
  57. SUM(CASE WHEN status >= 400 THEN 1 ELSE 0 END) AS error_count,
  58. COUNT(*) AS sample_count`
  59. return `
  60. WITH normalized AS (
  61. SELECT
  62. from_iso8601_timestamp(event_timestamp) AS event_time,
  63. model AS raw_model,
  64. ${statModelSql("model", "provider_model")} AS model,
  65. COALESCE(NULLIF(provider_model, ''), '') AS provider_model,
  66. COALESCE(NULLIF(provider, ''), '') AS raw_provider,
  67. UPPER(COALESCE(NULLIF(cf_country, ''), 'ZZ')) AS country,
  68. COALESCE(NULLIF(cf_continent, ''), '') AS continent,
  69. session,
  70. status,
  71. duration AS duration_ms,
  72. time_to_first_byte AS ttfb_ms,
  73. timestamp_first_byte,
  74. timestamp_last_byte,
  75. tokens_input,
  76. tokens_output,
  77. tokens_reasoning,
  78. tokens_cache_read,
  79. tokens_cache_write_5m,
  80. tokens_cache_write_1h,
  81. cost_input_microcents,
  82. cost_output_microcents,
  83. cost_total_microcents,
  84. cost_input,
  85. cost_output,
  86. cost_total,
  87. source
  88. FROM ${sourceTable}
  89. WHERE event_type = 'completions'
  90. AND model IS NOT NULL
  91. AND model <> ''
  92. AND event_timestamp >= ${periodStartValue}
  93. AND event_timestamp < ${periodEndValue}
  94. ), filtered AS (
  95. SELECT
  96. event_time,
  97. CASE
  98. WHEN source = 'lite' THEN 'Go'
  99. WHEN raw_model IN ('gpt-5-nano', 'grok-code', 'big-pickle') OR regexp_like(raw_model, '-free(:global)?$') THEN 'Free'
  100. ELSE 'Paid'
  101. END AS tier,
  102. ${statProviderSql("model", "provider_model", "raw_provider")} AS provider,
  103. provider_model,
  104. model,
  105. country,
  106. continent,
  107. session,
  108. status,
  109. duration_ms,
  110. ttfb_ms,
  111. CASE
  112. WHEN timestamp_last_byte - timestamp_first_byte < 100 THEN null
  113. ELSE CAST(tokens_output AS double) / (timestamp_last_byte - timestamp_first_byte) * 1000
  114. END AS output_tps,
  115. tokens_input,
  116. tokens_output,
  117. tokens_reasoning,
  118. tokens_cache_read,
  119. COALESCE(tokens_cache_read, 0) + COALESCE(tokens_cache_write_5m, 0) + COALESCE(tokens_cache_write_1h, 0) + COALESCE(tokens_input, 0) + COALESCE(tokens_output, 0) AS tokens_total,
  120. COALESCE(cost_input_microcents, cost_input * 1000000) AS cost_input_microcents,
  121. COALESCE(cost_output_microcents, cost_output * 1000000) AS cost_output_microcents,
  122. COALESCE(cost_total_microcents, cost_total * 1000000) AS cost_total_microcents
  123. FROM normalized
  124. WHERE lower(model) NOT IN (${[...EXCLUDED_MODELS].map(sqlString).join(", ")})
  125. ), weekly AS (
  126. SELECT
  127. concat(CAST(year_of_week(event_time) AS varchar), '-W', lpad(CAST(week(event_time) AS varchar), 2, '0')) AS week_key,
  128. *
  129. FROM filtered
  130. ), daily AS (
  131. SELECT substr(to_iso8601(date_trunc('day', event_time)), 1, 10) AS day_key, *
  132. FROM filtered
  133. )
  134. SELECT
  135. 'week' AS grain,
  136. week_key AS period_key,
  137. ${sqlString(Resource.StatsSyncConfig.dataset)} AS dataset,
  138. tier,
  139. ${dimensionSql.select},
  140. ${aggregateColumns}
  141. FROM weekly
  142. GROUP BY week_key, tier, ${dimensionSql.groupBy}
  143. UNION ALL
  144. SELECT
  145. 'day' AS grain,
  146. day_key AS period_key,
  147. ${sqlString(Resource.StatsSyncConfig.dataset)} AS dataset,
  148. tier,
  149. ${dimensionSql.select},
  150. ${aggregateColumns}
  151. FROM daily
  152. GROUP BY day_key, tier, ${dimensionSql.groupBy}
  153. ORDER BY grain, period_key, total_tokens DESC
  154. `
  155. }
  156. export function toModelAggregate(data: AthenaData): ModelStatAggregate[] {
  157. const model = statModel(data.model, data.provider_model)
  158. const provider = statProvider(model, data.provider_model, data.provider)
  159. if (!provider) return []
  160. return toStatBaseAggregate(data).flatMap((base) => [
  161. { ...base, provider, model, provider_model: data.provider_model || "" },
  162. ])
  163. }
  164. export function toProviderAggregate(data: AthenaData): ProviderStatAggregate[] {
  165. return toStatBaseAggregate(data).flatMap((base) => [
  166. { ...base, provider: statProvider(data.model, data.provider_model, data.provider) || "unknown" },
  167. ])
  168. }
  169. export function toGeoAggregate(data: AthenaData): GeoStatAggregate[] {
  170. return toStatBaseAggregate(data).flatMap((base) => [
  171. {
  172. ...base,
  173. provider: statProvider(data.model, data.provider_model, data.provider) || "all",
  174. model: statModel(data.model || "all", data.provider_model),
  175. country: normalizeCountry(data.country),
  176. continent: data.continent || "",
  177. },
  178. ])
  179. }
  180. function toStatBaseAggregate(data: AthenaData): StatBaseAggregate[] {
  181. const grain = data.grain === "day" || data.grain === "week" ? data.grain : undefined
  182. if (!grain || !data.period_key) return []
  183. return [
  184. {
  185. grain,
  186. period_key: data.period_key,
  187. dataset: data.dataset || Resource.StatsSyncConfig.dataset,
  188. tier: normalizeTier(data.tier || "unknown"),
  189. sessions: integer(data, "sessions"),
  190. requests: integer(data, "requests"),
  191. input_tokens: integer(data, "input_tokens"),
  192. output_tokens: integer(data, "output_tokens"),
  193. reasoning_tokens: integer(data, "reasoning_tokens"),
  194. cache_read_tokens: integer(data, "cache_read_tokens"),
  195. total_tokens: integer(data, "total_tokens"),
  196. input_cost_microcents: integer(data, "input_cost_microcents"),
  197. output_cost_microcents: integer(data, "output_cost_microcents"),
  198. total_cost_microcents: integer(data, "total_cost_microcents"),
  199. avg_duration_ms: nullableNumber(data, "avg_duration_ms"),
  200. p50_duration_ms: nullableInteger(data, "p50_duration_ms"),
  201. p95_duration_ms: nullableInteger(data, "p95_duration_ms"),
  202. avg_ttfb_ms: nullableNumber(data, "avg_ttfb_ms"),
  203. p50_ttfb_ms: nullableInteger(data, "p50_ttfb_ms"),
  204. p95_ttfb_ms: nullableInteger(data, "p95_ttfb_ms"),
  205. avg_output_tps: nullableNumber(data, "avg_output_tps"),
  206. success_count: integer(data, "success_count"),
  207. error_count: integer(data, "error_count"),
  208. sample_count: integer(data, "sample_count"),
  209. },
  210. ]
  211. }
  212. function integer(data: AthenaData, key: string) {
  213. return Math.round(number(data, key))
  214. }
  215. function nullableNumber(data: AthenaData, key: string) {
  216. if (data[key] === undefined || data[key] === "") return null
  217. return Number(number(data, key).toFixed(2))
  218. }
  219. function nullableInteger(data: AthenaData, key: string) {
  220. if (data[key] === undefined || data[key] === "") return null
  221. return Math.round(number(data, key))
  222. }
  223. function number(data: AthenaData, key: string) {
  224. const value = Number(data[key])
  225. return Number.isFinite(value) ? value : 0
  226. }
  227. function sqlIdentifier(value: string) {
  228. return `"${value.replace(/"/g, '""')}"`
  229. }
  230. function sqlString(value: string) {
  231. return `'${value.replace(/'/g, "''")}'`
  232. }
  233. function statModelSql(model: string, providerModel: string) {
  234. return `COALESCE(NULLIF(regexp_replace(CASE
  235. WHEN lower(${model}) = 'big-pickle' THEN NULLIF(${providerModel}, '')
  236. ELSE ${model}
  237. END, '(-free|:global)+$', ''), ''), 'unknown')`
  238. }
  239. function statProviderSql(model: string, providerModel: string, provider: string) {
  240. return `CASE
  241. ${MODEL_AUTHOR_RULES.map((item) => ` WHEN strpos(lower(${providerModel}), ${sqlString(item.match)}) > 0 THEN ${sqlString(item.author)}`).join("\n")}
  242. ${MODEL_AUTHOR_RULES.map((item) => ` WHEN strpos(lower(${model}), ${sqlString(item.match)}) > 0 THEN ${sqlString(item.author)}`).join("\n")}
  243. WHEN ${provider} <> '' AND lower(${provider}) NOT IN (${RETIRED_STAT_PROVIDERS.map(sqlString).join(", ")}) THEN ${provider}
  244. ELSE 'unknown'
  245. END`
  246. }