{"id":"4e89917c-a0a1-4467-9d3f-4b99932d4bf7","entityType":"agent","slug":"clawhub-kwdb-kwdb-text2sql-aiot","name":"KWDB Text2SQL AIoT","canonicalUrl":"https://www.xpersona.co/agent/clawhub-kwdb-kwdb-text2sql-aiot","canonicalPath":"/agent/clawhub-kwdb-kwdb-text2sql-aiot","generatedAt":"2026-10-11T04:33:34.439Z","source":"CLAWHUB","claimStatus":"UNCLAIMED","verificationTier":"NONE","summary":{"evidence":{"source":"CLAWHUB","verified":false,"confidence":"medium","updatedAt":"2026-10-11T01:57:15.025Z","emptyReason":null},"description":"Convert natural language queries to KWDB SQL for time series data, relational data and cross-model analysis. Use this skill whenever users ask to query KWDB databases, write SQL for KWDB, or convert natural language to KWDB-specific SQL syntax. Supports: CREATE DATABASE/TABLE, downsampling, interpolation, latest value queries, aggregation analysis, cross-model queries, window/session/event analysis.","descriptionLabel":"Source description","evidenceSummary":"Capability contract not published. No trust telemetry is available yet. 1.2K downloads reported by the source. Last updated 10/11/2026.","installCommand":"clawhub skill install s17e2f9k2hwm2p7q50y9wz03fs8445dh:kwdb-text2sql-aiot","sourceUrl":"https://clawhub.ai/kwdb/kwdb-text2sql-aiot","homepage":"https://clawhub.ai/kwdb/skills/kwdb-text2sql-aiot","primaryLinks":[{"label":"View on ClawHub","url":"https://clawhub.ai/kwdb/kwdb-text2sql-aiot","kind":"source"},{"label":"Homepage","url":"https://clawhub.ai/kwdb/skills/kwdb-text2sql-aiot","kind":"homepage"}],"safetyScore":84,"overallRank":62,"popularityScore":62,"trustScore":null,"claimedByName":null,"isOwner":false,"seoDescription":"KWDB Text2SQL AIoT technical dossier on Xpersona with agent coverage, OPENCLEW support, and live trust metadata."},"coverage":{"evidence":{"source":"public-profile","verified":false,"confidence":"medium","updatedAt":"2026-10-11T01:57:15.025Z","emptyReason":null},"protocols":[{"protocol":"OPENCLEW","label":"OpenClaw","status":"self-declared","notes":"Declared in the public agent profile."}],"capabilities":[],"verifiedCount":0,"selfDeclaredCount":1,"capabilityMatrix":{"rows":[{"key":"OPENCLEW","type":"protocol","support":"unknown","confidenceSource":"profile","notes":"Listed on profile"}],"flattenedTokens":"protocol:OPENCLEW|unknown|profile"}},"adoption":{"evidence":{"source":"CLAWHUB","verified":false,"confidence":"medium","updatedAt":"2026-10-11T01:57:15.025Z","emptyReason":null},"stars":null,"forks":null,"downloads":1198,"packageName":null,"latestVersion":"1.2.1","tractionLabel":"1.2K downloads"},"release":{"evidence":{"source":"CLAWHUB","verified":false,"confidence":"medium","updatedAt":"2026-10-11T01:57:14.959Z","emptyReason":null},"lastUpdatedAt":"2026-10-11T01:57:15.025Z","lastCrawledAt":"2026-10-11T01:57:14.959Z","lastIndexedAt":null,"nextCrawlAt":"2026-10-12T01:57:14.959Z","lastVerifiedAt":null,"highlights":[{"version":"1.2.1","createdAt":"2026-08-28T00:47:24.722Z","changelog":"- Removed the informational file skill-card.md from the project. - No changes were made to the core skill logic or user-facing functionality.","fileCount":15,"zipByteSize":29868},{"version":"1.2.0","createdAt":"2026-07-23T05:52:08.522Z","changelog":"- Removed the file skill-card.md from the skill package. - No changes to logic, documentation, or behavior. - Skill functionality, triggers, description, and workflow remain unchanged.","fileCount":15,"zipByteSize":29727},{"version":"1.1.0","createdAt":"2026-06-22T09:00:54.970Z","changelog":"**Improved workflow and detailed documentation for KWDB natural language to SQL conversion** - Expanded skill description, supported query types, and explicit triggers for various KWDB use cases. - Documented a step-by-step workflow: from MCP schema discovery, query type routing, SQL generation, result formatting, to KWDB execution and error handling. - Included decision trees for query routing and guidance on schema handling with/without MCP integration. - Listed quick reference NL-to-SQL examples and function usage patterns. - Added guardrails for schema verification, time range checks, and output validation. - Provided detailed instructions for error diagnosis and regeneration when SQL execution fails.","fileCount":15,"zipByteSize":29734},{"version":"1.0.0","createdAt":"2026-05-13T04:02:10.943Z","changelog":"Initial release of the KWDB Text-to-SQL AIoT skill for converting natural language to KWDB SQL: - Supports time series and relational data queries, including downsampling, interpolation, latest value, aggregation, and cross-model analysis. - Outlines a multi-phase workflow: schema discovery (with/fallback MCP), query routing, SQL generation, output formatting, and KWDB execution. - Includes quick-reference mappings for natural language to SQL patterns and function usage. - Provides guardrails for schema assumptions, user clarifications, and error handling with an authoritative error reference. - Designed to guide users through querying KWDB databases, from NL input to validated SQL and execution.","fileCount":15,"zipByteSize":29887}]},"execution":{"evidence":{"source":"CLAWHUB","verified":false,"confidence":"low","updatedAt":null,"emptyReason":"No published capability contract is available yet."},"installCommand":"clawhub skill install s17e2f9k2hwm2p7q50y9wz03fs8445dh:kwdb-text2sql-aiot","setupComplexity":"low","setupSteps":["Install using `clawhub skill install s17e2f9k2hwm2p7q50y9wz03fs8445dh:kwdb-text2sql-aiot` in an isolated environment before connecting it to live workloads.","No published capability contract is available yet, so validate auth and request/response behavior manually.","Review the upstream CLAWHUB listing at https://clawhub.ai/kwdb/kwdb-text2sql-aiot before using production credentials."],"contract":{"contractStatus":"missing","authModes":[],"requires":[],"forbidden":[],"supportsMcp":false,"supportsA2a":false,"supportsStreaming":false,"inputSchemaRef":null,"outputSchemaRef":null,"dataRegion":null,"contractUpdatedAt":null,"sourceUpdatedAt":null,"freshnessSeconds":null},"invocationGuide":{"preferredApi":{"snapshotUrl":"https://www.xpersona.co/api/v1/agents/clawhub-kwdb-kwdb-text2sql-aiot/snapshot","contractUrl":"https://www.xpersona.co/api/v1/agents/clawhub-kwdb-kwdb-text2sql-aiot/contract","trustUrl":"https://www.xpersona.co/api/v1/agents/clawhub-kwdb-kwdb-text2sql-aiot/trust"},"curlExamples":["curl -s \"https://www.xpersona.co/api/v1/agents/clawhub-kwdb-kwdb-text2sql-aiot/snapshot\"","curl -s \"https://www.xpersona.co/api/v1/agents/clawhub-kwdb-kwdb-text2sql-aiot/contract\"","curl -s \"https://www.xpersona.co/api/v1/agents/clawhub-kwdb-kwdb-text2sql-aiot/trust\""],"jsonRequestTemplate":{"query":"summarize this repo","constraints":{"maxLatencyMs":2000,"protocolPreference":["OPENCLEW"]}},"jsonResponseTemplate":{"ok":true,"result":{"summary":"...","confidence":0.9},"meta":{"source":"CLAWHUB","generatedAt":"2026-10-11T04:33:34.437Z"}},"retryPolicy":{"maxAttempts":3,"backoffMs":[500,1500,3500],"retryableConditions":["HTTP_429","HTTP_503","NETWORK_TIMEOUT"]}},"endpoints":{"dossierUrl":"https://www.xpersona.co/api/v1/agents/clawhub-kwdb-kwdb-text2sql-aiot/dossier","snapshotUrl":"https://www.xpersona.co/api/v1/agents/clawhub-kwdb-kwdb-text2sql-aiot/snapshot","contractUrl":"https://www.xpersona.co/api/v1/agents/clawhub-kwdb-kwdb-text2sql-aiot/contract","trustUrl":"https://www.xpersona.co/api/v1/agents/clawhub-kwdb-kwdb-text2sql-aiot/trust"}},"reliability":{"evidence":{"source":"runtime-metrics","verified":false,"confidence":"low","updatedAt":null,"emptyReason":"No trust, reliability, or runtime telemetry is available."},"trust":{"status":"unavailable","handshakeStatus":"UNKNOWN","verificationFreshnessHours":null,"reputationScore":null,"p95LatencyMs":null,"successRate30d":null,"fallbackRate":null,"attempts30d":null,"trustUpdatedAt":null,"trustConfidence":"unknown","sourceUpdatedAt":null,"freshnessSeconds":null},"decisionGuardrails":{"doNotUseIf":["Contract metadata is missing or unavailable for deterministic execution."],"safeUseWhen":[],"riskFlags":["missing_or_unavailable_contract","trust_data_unavailable","schema_references_missing"],"operationalConfidence":"low"},"executionMetrics":{"observedLatencyMsP50":null,"observedLatencyMsP95":null,"estimatedCostUsd":null,"uptime30d":null,"rateLimitRpm":null,"rateLimitBurst":null,"lastVerifiedAt":null,"verificationSource":null},"runtimeMetrics":{"successRate":null,"avgLatencyMs":null,"avgCostUsd":null,"hallucinationRate":null,"retryRate":null,"disputeRate":null,"p50Latency":null,"p95Latency":null,"lastUpdated":null}},"benchmarks":{"evidence":{"source":"no-benchmark-data","verified":false,"confidence":"low","updatedAt":null,"emptyReason":"No benchmark suites or observed failure patterns are available."},"suites":[],"failurePatterns":[]},"artifacts":{"evidence":{"source":"CLAWHUB","verified":false,"confidence":"medium","updatedAt":"2026-10-11T01:57:15.025Z","emptyReason":null},"readme":"Skill: KWDB Text2SQL AIoT\n\nOwner: kwdb\n\nSummary: Convert natural language queries to KWDB SQL for time series data, relational data and cross-model analysis. Use this skill whenever users ask to query KWDB databases, write SQL for KWDB, or convert natural language to KWDB-specific SQL syntax. Supports: CREATE DATABASE/TABLE, downsampling, interpolation, latest value queries, aggregation analysis, cross-model queries, window/session/event analysis.\n\nTags: kwdb:1.2.1, latest:1.2.1, ready:1.2.1\n\nVersion history:\n\nv1.2.1 | 2026-08-28T00:47:24.722Z | auto\n\n- Removed the informational file skill-card.md from the project.\n- No changes were made to the core skill logic or user-facing functionality.\n\nv1.2.0 | 2026-07-23T05:52:08.522Z | auto\n\n- Removed the file skill-card.md from the skill package.\n- No changes to logic, documentation, or behavior.\n- Skill functionality, triggers, description, and workflow remain unchanged.\n\nv1.1.0 | 2026-06-22T09:00:54.970Z | auto\n\n**Improved workflow and detailed documentation for KWDB natural language to SQL conversion**\n\n- Expanded skill description, supported query types, and explicit triggers for various KWDB use cases.\n- Documented a step-by-step workflow: from MCP schema discovery, query type routing, SQL generation, result formatting, to KWDB execution and error handling.\n- Included decision trees for query routing and guidance on schema handling with/without MCP integration.\n- Listed quick reference NL-to-SQL examples and function usage patterns.\n- Added guardrails for schema verification, time range checks, and output validation.\n- Provided detailed instructions for error diagnosis and regeneration when SQL execution fails.\n\nv1.0.0 | 2026-05-13T04:02:10.943Z | auto\n\nInitial release of the KWDB Text-to-SQL AIoT skill for converting natural language to KWDB SQL:\n\n- Supports time series and relational data queries, including downsampling, interpolation, latest value, aggregation, and cross-model analysis.\n- Outlines a multi-phase workflow: schema discovery (with/fallback MCP), query routing, SQL generation, output formatting, and KWDB execution.\n- Includes quick-reference mappings for natural language to SQL patterns and function usage.\n- Provides guardrails for schema assumptions, user clarifications, and error handling with an authoritative error reference.\n- Designed to guide users through querying KWDB databases, from NL input to validated SQL and execution.\n\nArchive index:\n\nArchive v1.2.1: 15 files, 29868 bytes\n\nFiles: assets/output-template.md (5786b), references/cross-model.md (6146b), references/mcp-integration.md (3960b), references/relational-functions.md (9237b), references/relational.md (1950b), references/scenarios.md (2935b), references/ts-ddl.md (3719b), references/ts-downsampling.md (3628b), references/ts-functions.md (11764b), references/ts-interpolation.md (1929b), references/ts-latest-value.md (1965b), references/ts-window-events.md (4119b), skill-card.md (3104b), SKILL.md (9871b), _meta.json (137b)\n\nFile v1.2.1:SKILL.md\n\n---\nname: kwdb-text2sql-aiot\ndescription: |\n Convert natural language queries to KWDB SQL for time series data, relational data and cross-model analysis.\n Use this skill whenever users ask to query KWDB databases, write SQL for KWDB,\n or convert natural language to KWDB-specific SQL syntax.\n Supports: CREATE DATABASE/TABLE, downsampling, interpolation, latest value queries,\n aggregation analysis, cross-model queries, window/session/event analysis.\ntriggers:\n - query KWDB database\n - write SQL for KWDB\n - convert natural language to SQL\n - time series query\n - IoT sensor data query\n - downsampling query\n - interpolation query\n - latest value query\n - cross-model join query\n - 创建库/创建表/CREATE DATABASE/CREATE TABLE\n - 时序/降采样/插值/最新值/跨模\n---\n\n# KWDB Text-to-SQL Skill\n\n## Query Type Routing\n\nBased on the user's query, read the appropriate reference file:\n\n| Query Type | Reference File |\n|---------|---------------|\n| **Query routing (start here)** | `references/scenarios.md` |\n| MCP integration | `references/mcp-integration.md` |\n| 时序DDL (创建时序库/表) | `references/ts-ddl.md` |\n| 聚合操作及降采样 (每小时/每天统计) | `references/ts-downsampling.md` |\n| 插值/填充缺失值 | `references/ts-interpolation.md` |\n| 最新值查询 | `references/ts-latest-value.md` |\n| 滑动窗口/session/event | `references/ts-window-events.md` |\n| 关系表查询 | `references/relational.md` |\n| 跨模查询（时序表+关系表） | `references/cross-model.md` |\n| 时序函数语法速查 | `references/ts-functions.md` |\n| 关系函数语法速查 | `references/relational-functions.md` |\n\n## Quick Reference\n\n| NL Pattern | SQL Pattern |\n|------------|-------------|\n| 最近N分钟/小时/天的数据 | `WHERE ts >= NOW() - INTERVAL 'N hour'` |\n| 每小时/每天的平均值 | `time_bucket(ts, '1h/1d')` + `avg(col)` |\n| 每N分钟/小时/天降采样 | `time_bucket(ts, 'X')` + aggregation |\n| 填充缺失值 | `time_bucket_gapfill()` + `interpolate()` |\n| 最新数据 | `last(col)` or `ORDER BY ts DESC LIMIT 1` |\n| 滑动窗口 | `TIME_WINDOW(ts, '1h', '15m')` |\n| 关联设备信息 | `JOIN devices ON ...` |\n\n## Workflow\n\n### Phase 0: MCP Detection & Schema Discovery (Recommended)\n\n1. **Detect MCP availability**: Call `read-query` with `SELECT 1`\n - If successful → MCP is available\n - If failed → MCP is unavailable, proceed to fallback\n\n2. **Get database name** (if not provided by user):\n - Ask user: \"请提供要查询的数据库名称\"\n - Or execute `SHOW DATABASES` to list all databases\n\n3. **Discover tables in database**: Execute `SHOW TABLES FROM {database_name}`\n\n4. **Identify candidate tables**:\n - Match NL keywords to table names (e.g., \"传感器\" → sensor_data)\n - If multiple candidates → ask user: \"请确认表名: [A, B, C]?\"\n\n5. **Get table schema**: Execute `SHOW CREATE TABLE {database_name}.{table_name}`, do not use `DESCRIBE`\n - Note column names, types, primary key, tags, comments\n - Map NL field names to actual column names\n\n6. **Proceed to Phase 1** with verified schema\n\n### Phase 0 Fallback: No MCP Available\n\nWhen MCP is unavailable:\n\n1. **Option A - Ask user**: \"请提供表结构信息（表名、列名）\"\n - Wait for user to describe the schema\n - Proceed to Phase 1\n\n2. **Option B - Use assumed fields**: \"我将使用常见字段名生成 SQL，请验证\"\n - Use standard field names (ts, device_id, temperature, etc.)\n - Mark output as \"ASSUMED SCHEMA - please verify\"\n\n3. **Proceed to Phase 1**\n\n### Phase 1: Query Type Routing\n\n1. **Read scenarios.md**: `references/scenarios.md` - single entry point with decision tree\n2. **Route to scenario file** based on query type:\n - aggregation/downsampling → `ts-downsampling.md`\n - interpolation → `ts-interpolation.md`\n - latest value → `ts-latest-value.md`\n - window/session/event → `ts-window-events.md`\n - cross-model → `cross-model.md`\n - relational → `relational.md`\n3. **Function syntax** → see `ts-functions.md` (time-series) or `relational-functions.md` (relational)\n\n### Phase 2: SQL Generation\n\n1. **Extract entities**: Table name, columns, time range, conditions\n2. **Use schema from Phase 0** (if MCP was used)\n3. **Generate SQL**: Use patterns from reference to construct SQL\n4. **Validate**: Ensure SQL follows KWDB function syntax\n\n### Phase 3: Output\n\n1. **Format output**: Follow `assets/output-template.md`\n2. **Include field mapping** if MCP was used\n3. **Mark assumptions** if schema was assumed\n4. **Add verification checklist**\n\n### Phase 4: KWDB Execute\n\n**Prerequisite:** SQL has been generated in Phase 2 and formatted in Phase 3.\n\n#### Step 1: Check MCP Availability\n\n**Note:** If MCP was successfully used in Phase 0 and schema was discovered, MCP is available. If Phase 0 indicated MCP was unavailable, skip this phase entirely.\n\nIf MCP availability is unknown (e.g., Phase 0 was skipped), verify now:\n- Call `read-query` with `SELECT 1`\n- If successful → MCP is available, proceed to Step 2\n- If failed → MCP is unavailable, **skip this phase entirely** and end workflow\n\n#### Step 2: Ask User for Execution Confirmation\n\nPrompt user:\n```\n生成的 SQL 已准备就绪。是否需要通过 kwdb-mcp-server 执行该 SQL？\n- 输入 \"是\" 或 \"执行\" → 继续执行\n- 输入 \"否\" 或 \"跳过\" → 结束，不再执行\n```\n\nIf user declines → **end workflow**.\n\n#### Step 3: Determine Query Type\n\nAnalyze the generated SQL:\n- **Read query**: SELECT, SHOW, EXPLAIN → use `read-query`\n- **Write query**: INSERT, UPDATE, DELETE, CREATE, DROP, ALTER → use `write-query`\n\n#### Step 4: Execute Query\n\nCall the appropriate MCP tool:\n\n**For read queries (`read-query`):**\n```json\n{\n \"sql\": \"\"\n}\n```\n\n**For write queries (`write-query`):**\n```json\n{\n \"sql\": \"\"\n}\n```\n\n#### Step 5: Handle Execution Result\n\n**On Success:**\nReport to user:\n```\n## Execution Result\n- Status: success\n- Query Type: read / write\n- Row Count: N\n- Auto-Limited: true/false\n\n### Results\n[formatted table if applicable]\n```\n\n**On Failure:**\n1. Parse the error message to identify error type (see Error Type table in Error Handling section below)\n2. If error indicates **SQL generation issue** (wrong table name, wrong column, syntax error):\n - Explain to user: \"SQL 执行失败，正在分析错误原因...\"\n - Report the error and analysis:\n ```\n ## Execution Result\n - Status: failed\n - Error: [error message]\n - Analysis: [cause analysis]\n ```\n - Return to **Phase 1** with error context to regenerate SQL\n3. If error indicates **user data issue** (constraint violation, permission issue, etc.):\n - Report the error and suggest fixes, but do not auto-regenerate\n\n## Reference Files\n\n- `references/scenarios.md` - Query routing entry point (decision tree)\n- `references/mcp-integration.md` - How to use kwdb-mcp-server for schema discovery\n- `references/ts-ddl.md` - Time series DDL (CREATE DATABASE/TABLE with TAGS)\n- `references/ts-downsampling.md` - time_bucket for fixed-interval downsampling\n- `references/ts-interpolation.md` - time_bucket_gapfill + interpolate for gap filling\n- `references/ts-latest-value.md` - first/last/last_row for latest value queries\n- `references/ts-window-events.md` - TIME_WINDOW, SESSION_WINDOW, EVENT_WINDOW, TWA, diff\n- `references/relational.md` - Standard SQL for relational tables\n- `references/cross-model.md` - JOIN between relational and time series\n- `references/ts-functions.md` - KWDB time-series function syntax reference\n- `references/relational-functions.md` - KWDB relational function syntax reference\n\n\n## Guardrails\n\n1. **Always verify table existence** when MCP is available\n2. **Confirm column names** match actual schema before generating SQL\n3. **Ask for time range** if user doesn't specify\n4. **Add LIMIT clause** for queries without one (MCP auto-adds LIMIT 20, but you should be explicit)\n5. **Mark assumed schema** when MCP is unavailable\n6. **Handle ambiguous NL** by asking clarifying questions\n\n## Error Handling (Authoritative Reference)\n\nThis Error Type table is used by:\n- **Phase 4 Step 5** when SQL execution fails\n- **When user reports** that generated SQL failed\n\nWhen a user reports that generated SQL failed, diagnose and regenerate:\n\n| Error Type | Likely Cause | Fix |\n|-----------|-------------|-----|\n| `relation \"xxx\" does not exist` | Wrong table name | Ask user to confirm table name, re-discover via MCP |\n| `column \"xxx\" not found` | Wrong column name | Use MCP to re-read schema, update field mapping |\n| `syntax error` | SQL syntax issue | Review KWDB SQL syntax, check function parameter order |\n| `invalid interval` | Wrong interval format | Use format like `'1h'`, `'1d'`, `'5m'` — not复合格式 like `'1d1h'` |\n| Overflow / out of range | Aggregation result too large | Add filters to reduce result set size |\n| `ambiguous column reference` | Column name exists in both joined tables | Use fully-qualified column names (`table.column`) |\n| `permission denied` | No write permission | Report to user, do not regenerate |\n| `duplicate key` | Constraint violation | Report to user, do not regenerate |\n\nWhen SQL fails:\n1. Read the error message to identify the error type\n2. If schema issue → re-run MCP discovery\n3. If syntax issue → check `ts-functions.md` or `relational-functions.md` and relevant reference file\n4. If data issue → ask user for clarification\n5. Regenerate corrected SQL with explanation\n\n## Schema Discovery via MCP\n\nUse `read-query` tool to execute SHOW commands:\n\n| SQL Command | Purpose |\n|-------------|---------|\n| `SHOW DATABASES` | List all databases |\n| `SHOW TABLES FROM {database_name}` | List all tables in a database |\n| `SHOW CREATE TABLE {database_name}.{table_name}` | Get table structure (columns, types, tags, comments) |\n\nFile v1.2.1:_meta.json\n\n{\n \"ownerId\": \"kn7fr8q8f22jd6gzb6f7prf9g183z64v\",\n \"slug\": \"kwdb-text2sql-aiot\",\n \"version\": \"1.2.1\",\n \"publishedAt\": 1787878044722\n}\n\nFile v1.2.1:references/cross-model.md\n\n# Cross-Model Query Reference\n\nQueries that join relational and time-series tables in KWDB.\n\n## KWDB Multi-Model Architecture\n\n- **Relational Tables**: Standard SQL tables with primary keys\n- **Time-Series Tables**: Tables with timestamp and tag columns\n- **Cross-Model Queries**: JOIN between relational and time-series tables\n\n## Join Types Supported\n\n| Join Type | Keyword | Description |\n|-----------|---------|-------------|\n| Inner Join | `INNER JOIN` or `JOIN` | Only matching rows |\n| Left Join | `LEFT JOIN` | All left + matching right |\n| Right Join | `RIGHT JOIN` | Matching left + all right |\n| Full Join | `FULL JOIN` | All rows from both tables |\n\n### FULL JOIN Constraint\n\nWhen using `FULL JOIN`, **avoid subqueries in the join condition**:\n\n```sql\n-- Avoid (may cause issues):\nSELECT * FROM a FULL JOIN (SELECT ... FROM b WHERE ...) AS sub ON a.id = sub.id\n\n-- Prefer:\nSELECT * FROM a FULL JOIN b ON a.id = b.id\n```\n\n## Unsupported Joins\n\n- Cross Join (Cartesian product)\n\n## Subqueries Supported\n\nKWDB supports the following subquery types in cross-model queries:\n- **Correlated subquery**: Inner query depends on outer query results\n- **Non-correlated subquery**: Inner query runs independently, executes once\n- **Correlated scalar subquery**: Returns a single value based on outer query\n- **Non-correlated scalar subquery**: Independent, returns single value\n- **FROM subquery**: Full SQL query nested in FROM clause as a temp table\n\n## Common Patterns\n\n### Join on Primary Tag\n\nTime-series tables typically join on their primary tag:\n\n```sql\nrelational_table.id = timeseries_table.primary_tag\n```\n\n### Example: Device Info with Latest Readings\n\n```sql\n-- Input: \"Get device names with their latest temperature readings\"\nSELECT\n d.device_name,\n d.location,\n t.latest_temp,\n t.ts AS reading_time\nFROM devices d\nINNER JOIN (\n SELECT\n device_id,\n last(temperature) AS latest_temp,\n last(ts) AS ts\n FROM sensor_data\n GROUP BY device_id\n) t ON d.device_id = t.device_id;\n```\n\n### Example: Product Catalog with Sales Statistics\n\n```sql\n-- Input: \"Show product details with total sales in the last month\"\nSELECT\n p.product_id,\n p.product_name,\n p.category,\n COALESCE(s.total_quantity, 0) AS total_sold,\n COALESCE(s.total_revenue, 0) AS total_revenue\nFROM products p\nLEFT JOIN (\n SELECT\n product_id,\n sum(quantity) AS total_quantity,\n sum(quantity * price) AS total_revenue\n FROM sales\n WHERE sale_date >= NOW() - INTERVAL '1 month'\n GROUP BY product_id\n) s ON p.product_id = s.product_id\nORDER BY total_revenue DESC;\n```\n\n### Example: Location-Based Aggregation\n\n```sql\n-- Input: \"Calculate average temperature per location\"\nSELECT\n d.location,\n avg(t.temperature) AS avg_temp,\n count(*) AS reading_count\nFROM locations d\nINNER JOIN sensor_data t ON d.device_id = t.device_id\nWHERE t.ts >= NOW() - INTERVAL '24 hours'\nGROUP BY d.location\nORDER BY avg_temp DESC;\n```\n\n### Example: Real-Time Monitoring Dashboard\n\n```sql\n-- Input: \"Create a dashboard view with device status and current readings\"\nSELECT\n d.device_id,\n d.device_name,\n d.status AS device_status,\n s.temperature,\n s.humidity,\n s.pressure,\n s.ts AS last_update\nFROM devices d\nLEFT JOIN (\n SELECT\n device_id,\n last(temperature) AS temperature,\n last(humidity) AS humidity,\n last(pressure) AS pressure,\n last(ts) AS ts\n FROM sensor_data\n WHERE ts >= NOW() - INTERVAL '1 hour'\n GROUP BY device_id\n) s ON d.device_id = s.device_id\nORDER BY d.device_id;\n```\n\n### Example: Time-Series with Relational Filter\n\n```sql\n-- Input: \"Get temperature trends for active devices only\"\nSELECT\n time_bucket(t.ts, '1h') AS hour,\n d.device_name,\n avg(t.temperature) AS avg_temp\nFROM sensor_data t\nINNER JOIN devices d ON t.device_id = d.device_id\nWHERE d.status = 'active'\n AND t.ts >= NOW() - INTERVAL '7 days'\nGROUP BY hour, d.device_name\nORDER BY hour, d.device_name;\n```\n\n## Multi-Model Optimization\n\nKWDB optimizes cross-model queries by:\n\n1. Pushing aggregations to time-series engine\n2. Reducing data transfer between engines\n3. Using BatchLookupJoin for efficient joins\n\n### Enabling Optimization\n\n```sql\n-- Session level (default: enabled)\nSET enable_multimodel = true;\n\n-- Cluster level\nSET CLUSTER SETTING sql.defaults.multimodel.enabled = true;\n```\n\n## Template\n\n```sql\n-- Basic cross-model join\nSELECT\n r.,\n t.,\n t.\nFROM r\n[JOINTYPE] JOIN (\n SELECT\n ,\n () AS \n FROM \n WHERE >= ''\n GROUP BY \n) t ON r. = t.\n[WHERE ]\n[ORDER BY ];\n```\n\n## Union Queries\n\nKWDB also supports UNION-based set operations in cross-model queries:\n\n| Operation | Description |\n|-----------|-------------|\n| `UNION` | Combine results, remove duplicates |\n| `UNION ALL` | Combine results, keep all rows |\n| `INTERSECT` | Return rows in both results, remove duplicates |\n| `INTERSECT ALL` | Return rows in both results, keep duplicates |\n| `EXCEPT` | Return rows only in the first result, remove duplicates |\n| `EXCEPT ALL` | Return rows only in the first result, keep duplicates |\n\n**Example:**\n```sql\n-- List all devices that are either smart meters or have fault status\nSELECT deviceID, deviceName, 'smart_meter' AS category\nFROM rdb.Device\nWHERE modelID IN (101, 102)\nUNION ALL\nSELECT d.deviceID, d.deviceName, 'fault_device' AS category\nFROM rdb.Device d\nINNER JOIN tsdb.MonitoringCenter mc ON d.deviceID = mc.deviceID\nWHERE mc.status = -1\nORDER BY deviceID;\n```\n\n## Notes\n\n1. Join on Primary Tag columns for best performance\n2. Use LEFT JOIN when relational data might not have matching time-series\n3. Filter time-series data before joining when possible\n4. KWDB automatically optimizes cross-model queries\n5. Include appropriate WHERE clauses to limit data scope\n6. `FULL JOIN` does not support subqueries in join conditions — use direct table join instead\n\nFile v1.2.1:references/mcp-integration.md\n\n# KWDB MCP Server Integration\n\nThis guide describes how to use kwdb-mcp-server to automatically discover database schema and generate accurate SQL from natural language.\n\n## MCP Tools\n\n### read-query\n\nExecutes read-only SQL queries (SELECT, SHOW, EXPLAIN).\n\n**Parameters:**\n- `sql` (required) - The SQL query to execute\n\n**Returns:**\n```json\n{\n \"status\": \"success\",\n \"type\": \"query_result\",\n \"data\": {\n \"result_type\": \"table\",\n \"columns\": [\"col1\", \"col2\"],\n \"rows\": [{\"col1\": \"val1\", \"col2\": \"val2\"}],\n \"metadata\": {\n \"row_count\": 1,\n \"query\": \"SELECT ...\",\n \"auto_limited\": false\n }\n }\n}\n```\n\n**Note:** SELECT queries without LIMIT automatically get `LIMIT 20` added to prevent large result sets. Check `metadata.auto_limited` to detect this.\n\n## Schema Discovery via SHOW Commands\n\nUse `read-query` tool to execute SHOW commands for schema discovery:\n\n| SQL Command | Purpose |\n|-------------|---------|\n| `SHOW DATABASES` | List all databases |\n| `SHOW TABLES FROM {database_name}` | List all tables in a database |\n| `SHOW CREATE TABLE {database_name}.{table_name}` | Get complete table structure |\n\n\n\n## Workflow: Schema-Aware SQL Generation\n\n### Step 1: Detect MCP Availability\n\nCall `read-query` with `SELECT 1` to verify MCP is available.\n\n### Step 2: Get Database Name (if not provided)\n\nAsk user which database to query, or execute `SHOW DATABASES` to list all databases.\n\n### Step 3: Discover Tables\n\nExecute `SHOW TABLES FROM {database_name}` to get all tables in the database.\n\n### Step 4: Match Candidate Tables\n\nBased on natural language keywords, identify candidate tables:\n- \"设备\" / \"device\" / \"传感器\" / \"sensor\" → tables with device/sensor in name\n- \"温度\" / \"temperature\" → tables with temperature-related columns\n- \"历史\" / \"history\" → time-series tables\n\nIf multiple tables match, ask the user to confirm.\n\n### Step 5: Get Table Schema\n\nFor each candidate table, execute `SHOW CREATE TABLE {database_name}.{table_name}` to get column definitions.\n\n### Step 6: Map NL to Schema\n\nMap natural language field references to actual column names:\n- \"时间\" / \"timestamp\" → ts column\n- \"设备ID\" / \"device_id\" → tag columns\n- \"温度\" / \"temperature\" → measurement columns\n\n### Step 7: Generate SQL\n\nUse the schema information to construct accurate SQL.\n\n## Example\n\n**User query:** \"查询最近24小时每台设备的平均温度\"\n\n**MCP-assisted workflow:**\n\n1. Ask user for database name → \"iot_db\"\n\n2. Execute `SHOW DATABASES` to verify database exists\n\n3. Execute `SHOW TABLES FROM iot_db` → returns: [\"devices\", \"sensor_data\", \"alarms\"]\n\n4. Identify candidate tables: \"sensor_data\" likely contains temperature readings\n\n5. Execute `SHOW CREATE TABLE iot_db.sensor_data`:\n```sql\nCREATE TABLE iot_db.sensor_data (\n ts TIMESTAMPTZ NOT NULL,\n temperature DOUBLE,\n humidity DOUBLE,\n device_id INT4\n) TAGS (\n device_id INT4 NOT NULL,\n location VARCHAR(100)\n) PRIMARY TAGS (device_id)\n```\n\n6. Execute `SHOW CREATE TABLE iot_db.devices`:\n```sql\nCREATE TABLE iot_db.devices (\n device_id INT4 NOT NULL,\n device_name VARCHAR(100),\n location VARCHAR(100),\n PRIMARY KEY (device_id)\n)\n```\n\n7. Generate SQL:\n```sql\nSELECT d.device_name,\n d.location,\n AVG(s.temperature) AS avg_temp\nFROM devices d\nINNER JOIN (\n SELECT device_id,\n AVG(temperature) AS temperature\n FROM sensor_data\n WHERE ts >= NOW() - INTERVAL '24 hour'\n GROUP BY device_id\n) s ON d.device_id = s.device_id\nGROUP BY d.device_name, d.location\nORDER BY d.device_name;\n```\n\n## Fallback: No MCP Available\n\nWhen kwdb-mcp-server is not available:\n\n1. Ask user to manually provide table structure\n2. Or generate SQL with placeholder column names and mark as \"assumed schema\"\n3. User should verify and adjust the generated SQL\n\n## MCP Detection Pattern\n\nTo check if MCP is available, execute:\n\n```sql\nSELECT 1\n```\n\nIf this fails or returns an error, MCP is unavailable.\n\nFile v1.2.1:references/relational-functions.md\n\n# KWDB Relational Functions Reference\n\nRelational database functions for KWDB, following CockroachDB SQL dialect.\n\n## Conditional Functions\n\n- `COALESCE(val, ...)` - returns first non-NULL value\n- `IF(cond, then, else)` - conditional evaluation\n- `IFNULL(val, else)` - alias for COALESCE with two operands\n- `NULLIF(val1, val2)` - returns NULL if val1 equals val2, else val1\n- `CASE WHEN cond THEN val ... [ELSE val] END` - case expression\n\n## Comparison Functions\n\n- `between(val, low, high)` - val between low and high (inclusive)\n- `greatest(val, ...)` - maximum value from list\n- `least(val, ...)` - minimum value from list\n\n## Type Casting\n\n- `CAST(val AS type)` - cast value to type\n- `type::type` - PostgreSQL-style cast notation (e.g., `col::INT`)\n\n## Math Functions\n\n- `abs(val)` - absolute value\n- `avg(val)` - average (aggregate)\n- `ceil(val)` / `ceiling(val)` - round up\n- `cbrt(val)` - cube root\n- `div(val, divisor)` - integer division\n- `exp(val)` - e to the power of val\n- `floor(val)` - round down\n- `ln(val)` - natural logarithm\n- `log(val)` / `log(val, base)` - logarithm (base 10 if single arg)\n- `log2(val)` - logarithm base 2\n- `log10(val)` - logarithm base 10\n- `max(val)` - maximum (aggregate)\n- `min(val)` - minimum (aggregate)\n- `mod(val, divisor)` - modulo remainder\n- `pi()` - pi constant (3.14159...)\n- `power(val, exp)` / `pow(val, exp)` - val raised to power\n- `random()` - random value between 0 and 1\n- `round(val)` - round to nearest integer\n- `setseed(val)` - set random seed\n- `sign(val)` - sign of value (-1, 0, 1)\n- `sqrt(val)` - square root\n- `sum(val)` - sum (aggregate)\n- `trunc(val)` - truncate decimal part\n\n## Trigonometric Functions\n\n- `acos(val)` - arc cosine\n- `asin(val)` - arc sine\n- `atan(val)` - arc tangent\n- `atan2(y, x)` - arc tangent of y/x\n- `cos(val)` - cosine\n- `cot(val)` - cotangent\n- `degrees(val)` - radians to degrees\n- `radians(val)` - degrees to radians\n- `sin(val)` - sine\n- `tan(val)` - tangent\n\n## Hyperbolic Functions\n\n- `sinh(val)` - hyperbolic sine\n- `cosh(val)` - hyperbolic cosine\n- `tanh(val)` - hyperbolic tangent\n- `arcsinh(val)` - inverse hyperbolic sine\n- `arccosh(val)` - inverse hyperbolic cosine\n- `arctanh(val)` - inverse hyperbolic tangent\n\n## String Functions\n\n- `char_length(val)` / `character_length(val)` - character count\n- `concat(val, ...)` - concatenate values\n- `concat_ws(sep, val, ...)` - concatenate with separator\n- `initcap(string)` - capitalize first letter of each word\n- `length(string)` - character length\n- `lower(string)` - convert to lowercase\n- `lpad(string, length)` / `lpad(string, length, fill)` - pad left\n- `octet_length(val)` - byte length\n- `bit_length(val)` - bit length\n- `replace(string, from, to)` - replace substring\n- `reverse(string)` - reverse string\n- `rpad(string, length)` / `rpad(string, length, fill)` - pad right\n- `left(string, n)` - first n characters\n- `right(string, n)` - last n characters\n- `rtrim(string)` / `rtrim(string, chars)` - trim right\n- `ltrim(string)` / `ltrim(string, chars)` - trim left\n- `btrim(string)` / `btrim(string, chars)` - trim both sides\n- `split_part(string, delim, n)` - split and return nth part\n- `strpos(string, substring)` - position of substring\n- `substring(string, start)` / `substring(string, start, len)` - extract substring\n- `trim(LEADING|TRAILING|BOTH chars FROM string)` - trim characters\n- `upper(string)` - convert to uppercase\n- `overlay(string PLACING new FROM start FOR count)` - replace substring\n- `format(text, val, ...)` - format string (printf-style)\n- `md5(string)` - MD5 hash\n- `sha256(string)` - SHA-256 hash\n- `chr(val)` - character from ASCII code\n\n## Array Functions\n\n- `array_append(array, elem)` - append element\n- `array_prepend(elem, array)` - prepend element\n- `array_cat(left, right)` - concatenate arrays\n- `array_dims(array)` - dimensions as text\n- `array_length(array, dim)` - length of dimension\n- `array_lower(array, dim)` - lower bound\n- `array_upper(array, dim)` - upper bound\n- `array_to_string(array, sep)` - array to string\n- `cardinality(array)` - element count\n- `string_to_array(string, sep)` / `string_to_array(string, sep, null)` - split to array\n- `unnest(array)` - expand array to rows\n- `generate_series(start, stop)` / `generate_series(start, stop, step)` - generate series\n\n## Date and Time Functions\n\n- `age(timestamp)` - interval between timestamp and current date\n- `current_date` - current date\n- `current_time` - current time\n- `current_timestamp` - current timestamp\n- `localtime` - current local time\n- `localtimestamp` - current local timestamp\n- `clock_timestamp()` - current timestamp (变化)\n- `now()` - current timestamp\n- `statement_timestamp()` - statement start time\n- `transaction_timestamp()` - transaction start time\n- `timeofday()` - current time as text\n- `date_part(text, timestamp)` - extract part\n- `date_trunc(text, timestamp)` - truncate to precision\n- `extract(part FROM timestamp)` - extract field\n- `make_date(year, month, day)` - create date\n- `make_time(hour, min, sec)` - create time\n- `make_timestamp(...)` - create timestamp\n- `make_interval(...)` - create interval\n- `make_timestamptz(...)` - create timestamptz\n- `isfinite_date(date)` / `isfinite_timestamp(timestamp)` - check if finite\n\n## ID Generation Functions\n\n- `gen_random_uuid()` - generate random UUID\n- `uuid_generate_v4()` - generate UUID v4\n\n## Network Functions\n\n- `host(inet)` - extract host from inet\n- `masklen(inet)` - mask length\n- `netmask(inet)` - network mask\n- `network(inet)` - network address\n\n## JSONB Functions\n\n- `jsonb_build_array(...)` - build JSON array\n- `jsonb_build_object(...)` - build JSON object\n- `jsonb_extract_path(jsonb, path)` - extract path\n- `jsonb_object_keys(jsonb)` - object keys\n- `jsonb_populate_record(record, jsonb)` - populate record\n- `jsonb_pretty(jsonb)` - formatted JSON\n- `jsonb_set(jsonb, path, value)` - set value\n- `jsonb_typeof(jsonb)` - JSON type\n- `jsonb_each(jsonb)` - expand object to rows\n\n## System Information Functions\n\n- `current_catalog()` / `current_database()` - current database\n- `current_schema()` - current schema\n- `current_schemas(boolean)` - visible schemas\n- `current_user()` / `session_user()` / `user` - current user\n- `version()` - database version\n- `current_setting(name)` - get setting\n- `set_config(name, value, is_local)` - set configuration\n- `pg_column_size(any)` - column size in bytes\n- `pg_database_size(oid)` - database size\n- `pg_relation_size(relation)` - relation size\n- `pg_table_size(relation)` - table size\n- `pg_indexes_size(relation)` - indexes size\n- `pg_typeof(val)` - type of value\n- `pg_encoding_to_char(encoding)` - encoding name\n- `quote_ident(val)` - properly quoted identifier\n- `quote_literal(val)` - properly quoted literal\n\n## Sequence Functions\n\n- `nextval(regclass)` - next value in sequence\n- `currval(regclass)` - last value returned\n- `lastval()` - last value returned\n- `setval(regclass, count)` / `setval(regclass, count, is_called)` - set sequence value\n\n## Aggregate Functions\n\n- `array_agg(val)` - collect values into array\n- `avg(val)` - average\n- `bit_and(val)` - bitwise AND\n- `bit_or(val)` - bitwise OR\n- `bit_xor(val)` - bitwise XOR\n- `bool_and(val)` / `every(val)` - boolean AND\n- `bool_or(val)` - boolean OR\n- `count(*)` - count all rows\n- `count(val)` - count non-NULL values\n- `jsonb_agg(val)` - aggregate to JSON array\n- `jsonb_object_agg(key, value)` - aggregate to JSON object\n- `string_agg(val, separator)` - concatenate with separator\n- `sum(val)` - sum\n- `stddev(val)` / `stddev_pop(val)` / `stddev_samp(val)` - standard deviation\n- `variance(val)` / `var_pop(val)` / `var_samp(val)` - variance\n\n## Window Functions\n\n- `row_number()` - sequential row number\n- `rank()` - rank with gaps\n- `dense_rank()` - rank without gaps\n- `percent_rank()` - relative rank (0-1)\n- `cume_dist()` - cumulative distribution\n- `ntile(n)` - divide into n buckets\n- `lag(val)` / `lag(val, n)` / `lag(val, n, default)` - previous row value\n- `lead(val)` / `lead(val, n)` / `lead(val, n, default)` - next row value\n- `first_value(val)` - first value in window\n- `last_value(val)` - last value in window\n- `nth_value(val, n)` - nth value in window\n\n## Special SQL Syntax Forms\n\n| Special Form | Equivalent To |\n|--------------|---------------|\n| `AT TIME ZONE` | `timezone()` |\n| `CURRENT_CATALOG` | `current_database()` |\n| `CURRENT_DATE` | `current_date()` |\n| `CURRENT_ROLE` | `current_user()` |\n| `CURRENT_SCHEMA` | `current_schema()` |\n| `CURRENT_TIMESTAMP` | `current_timestamp()` |\n| `CURRENT_TIME` | `current_time()` |\n| `CURRENT_USER` | `current_user()` |\n| `EXTRACT(part FROM value)` | `extract(part, value)` |\n| `EXTRACT_DURATION(part FROM value)` | `extract_duration(part, value)` |\n| `OVERLAY(text1 PLACING text2 FROM int1 FOR int2)` | `overlay(text1, text2, int1, int2)` |\n| `SUBSTRING(text FROM start FOR count)` | `substr(text, start, count)` |\n| `TRIM(LEADING\\|TRAILING\\|BOTH chars FROM text)` | `ltrim/rtrim/btrim(text, chars)` |\n| `POSITION(text1 IN text2)` | `strpos(text2, text1)` |\n| `NEXTVAL(seq)` | `nextval(seq)` |\n| `DATE_TRUNC(text, timestamp)` | `date_trunc(text, timestamp)` |\n| `TREAT(expr AS type)` | type cast |\n| `session_user` | `current_user()` |\n| `COLLATION FOR` | `pg_collation_for()` |\n\nFile v1.2.1:references/relational.md\n\n# Relational Query Reference\n\nStandard SQL patterns for KWDB relational tables.\n\n## Basic SELECT\n\n```sql\nSELECT column1, column2 FROM table_name WHERE condition;\nSELECT * FROM table_name; -- all columns\n```\n\n## Filtering\n\n```sql\nWHERE column = value\nWHERE column > value\nWHERE column LIKE '%pattern%'\nWHERE column IN (val1, val2, val3)\nWHERE column IS NULL\nWHERE column IS NOT NULL\n```\n\n## Aggregation\n\n```sql\nSELECT count(*) FROM table_name;\nSELECT sum(column) FROM table_name;\nSELECT avg(column) FROM table_name;\nSELECT min(column), max(column) FROM table_name;\n```\n\n## GROUP BY\n\n```sql\nSELECT department, count(*) as cnt\nFROM employees\nGROUP BY department\nHAVING count(*) > 5;\n```\n\n## ORDER BY\n\n```sql\nORDER BY column ASC -- ascending (default)\nORDER BY column DESC -- descending\nORDER BY col1 ASC, col2 DESC\n```\n\n## Common Aggregate Functions\n\n- `count(*)` - count all rows\n- `count(column)` - count non-null values\n- `sum(column)` - sum of values\n- `avg(column)` - average of values\n- `min(column)` - minimum value\n- `max(column)` - maximum value\n\n## Natural Language Mapping\n\n| NL Pattern | SQL Pattern |\n|------------|-------------|\n| 查询所有数据 | `SELECT * FROM table` |\n| 按条件过滤 | `WHERE column = value` |\n| 按列分组统计 | `GROUP BY column` |\n| 分组后筛选 | `HAVING count(*) > N` |\n| 结果排序 | `ORDER BY column DESC` |\n| 统计总数 | `count(*)` |\n| 计算平均值 | `avg(column)` |\n\n## KWDB Relational Specifics\n\nKWDB's relational engine follows CockroachDB's SQL dialect. Key points:\n\n\n### Supported Relational Features\n\n- Standard SELECT/GROUP BY/HAVING/ORDER BY\n- JOINs (INNER, LEFT, RIGHT — see cross-model.md for full join details)\n- Subqueries (in FROM, WHERE, SELECT)\n- Common table expressions (WITH clause)\n- Window functions (ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, NTILE)\n- IMPORT for bulk data loading (DDL-level, not query-level)\n- Change Data Feed (CDC) for tracking changes\n\nFile v1.2.1:references/scenarios.md\n\n# Query Scenarios\n\nSingle entry point for routing natural language queries to the correct reference file.\n\n## Decision Tree\n\n```\nNL Query\n │\n ├─ Contains DDL keywords (CREATE, DROP, INSERT)? ──→ ts-ddl.md\n │\n ├─ \"最近\"/\"latest\"/\"最新\"/\"最近一条\"/\"current\" ──→ ts-latest-value.md\n │\n ├─ \"滑动\"/\"session\"/\"event\"/\"window\"/\"STATE_WINDOW\"/\"COUNT_WINDOW\" ──→ ts-window-events.md\n │\n ├─ \"填充\"/\"fill\"/\"gap\"/\"missing\"/\"插值\"/\"linear\" ──→ ts-interpolation.md\n │\n ├─ \"每小时\"/\"每天\"/\"每X分钟\" + 聚合词 ──→ ts-downsampling.md\n │\n ├─ JOIN 时序表 + 关系表? ──→ cross-model.md\n │\n └─ Default ──→ relational.md\n```\n\n## Keyword Mapping\n\n### DDL\n| CN | EN |\n|----|----|\n| 创建库 | CREATE DATABASE |\n| 创建表 | CREATE TABLE |\n| 时序库/时序表 | CREATE TABLE ... TAGS |\n| 添加标签 | ADD TAG |\n\n### Latest Value\n| CN | EN |\n|----|----|\n| 最新 | latest, last |\n| 最近一条 | most recent |\n| 当前 | current |\n\n### Interpolation\n| CN | EN |\n|----|----|\n| 填充 | fill |\n| 插值 | interpolate |\n| 缺失/gap | missing, gap |\n\n### Downsampling\n| CN | EN |\n|----|----|\n| 每小时 | hourly, every hour |\n| 每天 | daily, every day |\n| 平均值 | average |\n| 统计 | statistics |\n| 降采样 | downsampling |\n\n### Window Events\n| CN | EN |\n|----|----|\n| 滑动窗口 | sliding window |\n| session | session |\n| event | event |\n| 状态变化 | state change |\n\n## Quick Reference\n\n| Query Type | Reference | Key Function |\n|------------|-----------|--------------|\n| 创建时序库/表 | ts-ddl.md | `CREATE TS DATABASE`, `CREATE TABLE ... TAGS` |\n| 每小时/每天的平均值 | ts-downsampling.md | `time_bucket()` |\n| 填充缺失值 | ts-interpolation.md | `time_bucket_gapfill()` + `interpolate()` |\n| 最新/最近一条 | ts-latest-value.md | `last()`, `last_row()` |\n| 滑动窗口/session/event | ts-window-events.md | `TIME_WINDOW()`, `SESSION_WINDOW()`, `EVENT_WINDOW()` |\n| 时序表 + 关系表 JOIN | cross-model.md | JOIN |\n| 标准 SQL | relational.md | - |\n\n## Special Cases\n\n### TWA (Time Weighted Average)\n```\n\"计算时间加权平均温度\"\n```\n→ ts-window-events.md (TWA section)\n\n### diff() Function\n```\n\"计算温度变化率\"\n```\n→ ts-window-events.md (diff section)\n\n## Ambiguity Resolution\n\n1. **Ask user**: \"这是时序查询还是普通SQL查询?\"\n2. **Check schema**: 有 `ts`/`timestamp` 列和 `TAGS` → 时序查询\n3. **Default to time-series** if truly ambiguous\n\n## Fallback: Schema-Aware Routing\n\nWhen no keywords match, check table schema via MCP (if available):\n- Has `ts`/`timestamp` column + `TAGS` array → time-series query\n- Otherwise → relational.md\n\n## MCP Schema Check\n\nAfter routing, verify table type via MCP (if available):\n1. Read `kwdb://table/{table_name}`\n2. Check `table_type`: `\"TIME SERIES\"` 或 `\"relational\"`\n\nFile v1.2.1:references/ts-ddl.md\n\n# Time Series DDL Reference\n\nKWDB time series database and table creation patterns.\n\n## Create Time Series Database\n\n```sql\nCREATE TS DATABASE database_name;\n```\n\n## Create Time Series Table\n\n```sql\nCREATE TABLE database_name.table_name (\n ts TIMESTAMP NOT NULL, -- Timestamp column (required, must be first)\n column1 data_type, -- Data column\n column2 data_type\n) TAGS (\n tag1 data_type NOT NULL, -- Tag column (device identifier)\n tag2 data_type\n) PRIMARY TAGS (tag1);\n```\n\n### Example: Sensor Table\n\n```sql\nCREATE TABLE ts_db.sensors (\n ts TIMESTAMP NOT NULL,\n temperature DOUBLE,\n humidity DOUBLE,\n voltage DOUBLE\n) TAGS (\n device_id INT NOT NULL,\n location VARCHAR(100),\n device_type VARCHAR(50)\n) PRIMARY TAGS (device_id);\n```\n\n## Key Concepts\n\n### Timestamp Column\n- Must be `TIMESTAMP NOT NULL`\n- Must be the first column\n- Represents the time when data was recorded\n\n### Tag Columns\n- Device identifiers (device_id, location, etc.)\n- Used for partitioning and filtering\n- Can be indexed for fast lookups\n\n### Data Columns\n- Actual measurement values (temperature, humidity, etc.)\n- Stored as columns in the table\n\n### Primary Tags\n- Used for data partitioning across nodes\n- Should be the most frequently queried tag\n- One primary tag per table\n\n## Common Data Types\n\n| Type | Default Width | Max Width | Range / Description |\n|------|--------------|-----------|---------------------|\n| `TIMESTAMP` | - | - | 时间类型，支持精度 3(毫秒)/6(微秒)/9(纳秒)，默认3 |\n| `TIMESTAMPTZ` | - | - | 带时区的时间戳，存储时不包含时区数据，默认UTC |\n| `INT2` / `SMALLINT` | 2 字节 | - | -32768 ~ +32767 |\n| `INT4` / `INT` / `INTEGER` | 4 字节 | - | -2147483648 ~ +2147483647 |\n| `INT8` / `INT64` / `BIGINT` | 8 字节 | - | -9223372036854775808 ~ +9223372036854775807 |\n| `FLOAT4` / `REAL` | 4 字节 | - | 最大精度 17 位十进制小数 |\n| `FLOAT8` / `DOUBLE` / `DOUBLE PRECISION` | 8 字节 | - | 最大精度 17 位十进制小数 |\n| `BOOL` / `BOOLEAN` | 1 字节 | - | true / false |\n| `CHAR(n)` | 1 字节 | 1023 字节 | 定长字符，不足补空格，超长报错 |\n| `VARCHAR(n)` | 254 字节 | 65534 字节 | 变长字符，不足不补，超长报错 |\n| `NCHAR(n)` | 1 字符 | 254 字符 | 定长Unicode字符，不足补空格，超长报错 |\n| `NVARCHAR(n)` | 63 字符 | 16383 字符 | 变长Unicode字符，超长报错。**标签不支持该类型** |\n| `VARBYTES(n)` | 254 字节 | 65534 字节 | 变长二进制字符 |\n| `GEOMETRY` | - | - | 空间数据类型，支持 POINT/LINESTRING/POLYGON |\n\n### 数据类型转换\n\n| 原类型 | 支持转换的目标类型 |\n|--------|-------------------|\n| `INT2` | INT4, INT8, VARCHAR (最小宽度6) |\n| `INT4` | INT8, VARCHAR (最小宽度11) |\n| `INT8` | VARCHAR (最小宽度20) |\n| `FLOAT4` | FLOAT8, VARCHAR (最小宽度30) |\n| `FLOAT8` | VARCHAR (最小宽度30) |\n| `TIMESTAMP` | TIMESTAMPTZ, INT8, FLOAT4, FLOAT8 |\n| `TIMESTAMPTZ` | TIMESTAMP, INT8, FLOAT4, FLOAT8 |\n\n::: warning 说明\n- 转换后的数据类型宽度必须大于原数据类型。例如 INT4 可转 INT8，不可转 INT2。\n- 字符类型（CHAR/VARCHAR/NCHAR/NVARCHAR）支持同类型宽度转换，只能增加不能减少。\n- 标签列不支持 TIMESTAMP、TIMESTAMPTZ、NVARCHAR 类型。\n:::\n\n## Natural Language Mapping\n\n| NL Pattern | SQL Pattern |\n|------------|-------------|\n| 创建时序数据库 | `CREATE TS DATABASE name` |\n| 创建设备表 | `CREATE TABLE ... TAGS (device_id ...)` |\n| 创建传感器表 | `CREATE TABLE ... (ts, temperature, humidity)` |\n| 添加标签 | `TAGS (tag_name type)` |\n| 设置主标签 | `PRIMARY TAGS (tag_name)` |\n\nFile v1.2.1:references/ts-downsampling.md\n\n# Time Series Downsampling\n\nDownsampling time-series data by fixed time intervals using `time_bucket()`.\n\n## When to Use\n\nUse for: \"每小时的平均值\", \"每天的统计\", \"降采样到1分钟\"\n\n**Do NOT use `TIME_WINDOW()` here** — use `time_bucket()` for fixed-interval downsampling (performance optimized).\n\n## time_bucket Function\n\n```sql\ntime_bucket(timestamp_column, 'interval')\n```\n\n### Parameters\n\n| Parameter | Description |\n|-----------|-------------|\n| timestamp_column | The timestamp column (e.g., `ts`) |\n| interval | Support `ns`,`us`,`ms`,`s`,`m`,`h`,`day`,`week`,`mon`,`y` (e.g. `20ms`,`60s`) |\n\n## Examples\n\n**Hourly Average:**\n```sql\nSELECT time_bucket(ts, '1h') AS hour, avg(temperature) AS avg_temp\nFROM sensor_data\nWHERE ts >= NOW() - INTERVAL '1 day'\nGROUP BY hour\nORDER BY hour;\n```\n\n**Daily Max/Min:**\n```sql\nSELECT time_bucket(ts, '1d') AS day,\n max(temperature) AS max_temp,\n min(temperature) AS min_temp\nFROM sensor_data\nWHERE ts >= NOW() - INTERVAL '7 days'\nGROUP BY day\nORDER BY day;\n```\n\n**15-Minute Intervals:**\n```sql\nSELECT time_bucket(ts, '15m') AS bucket,\n device_id,\n avg(humidity) AS avg_humidity\nFROM sensor_data\nWHERE ts >= NOW() - INTERVAL '24 hours'\nGROUP BY bucket, device_id\nORDER BY bucket, device_id;\n```\n\n## Template\n\n```sql\nSELECT\n time_bucket(ts, '') AS period,\n ,\n () AS \nFROM \nWHERE ts >= NOW() - INTERVAL ''\nGROUP BY period, \nORDER BY period, ;\n```\n\n## Time Intervals\n\n| Interval | Keyword | Use Case |\n|----------|---------|----------|\n| 1 second | `'1s'` | High-frequency data |\n| 1 minute | `'1m'` | Real-time monitoring |\n| 5 minutes | `'5m'` | Standard monitoring |\n| 1 hour | `'1h'` | Hourly reports |\n| 1 day | `'1d'` | Daily aggregation |\n| 1 week | `'1w'` | Weekly reports |\n| 1 month | `'1mon'` | Monthly analysis |\n\n## time_bucket vs time_bucket_gapfill\n\n| 场景 | 推荐函数 |\n|------|---------|\n| 数据无缺失，正常时间对齐 | `time_bucket()` |\n| 数据存在缺失时间点，需返回完整时间序列 | `time_bucket_gapfill()` |\n| 缺失时间点需要补值（线性插值） | `time_bucket_gapfill()` + `interpolate()` |\n\n### 何时用 time_bucket\n\n`time_bucket` 仅对时间戳进行对齐，**不会填充缺失的时间桶**。适用于数据采集连续、无缺失的场景。\n\n- 查询每小时的平均值（数据完整）\n- 每天的统计汇总\n- 对数据做固定间隔的降采样\n\n### 何时用 time_bucket_gapfill\n\n`time_bucket_gapfill` 除了对齐时间戳外，**会自动填充缺失的时间桶行**。必须与 `GROUP BY` 配合使用。\n\n典型场景：\n- 设备定期上报数据，但某些时间点缺失，需要展示完整时间线\n- 绘制时间序列图时，需要保证每个时间间隔都有数据点\n- 配合 `interpolate()` 函数对缺失值进行线性补值\n\n```sql\n-- 使用 time_bucket_gapfill 填充缺失时间桶，配合 interpolate 补值\nSELECT\n time_bucket_gapfill(ts, '1h') AS hour,\n interpolate(temperature) AS temp\nFROM sensor_data\nWHERE ts >= NOW() - INTERVAL '1 day'\nGROUP BY hour\nORDER BY hour;\n```\n\n## Notes\n\n1. Use `time_bucket()` for fixed-interval downsampling (not `TIME_WINDOW`)\n2. Always include `GROUP BY time_bucket(...)` or `GROUP BY time_bucket_gapfill(...)`\n3. Use `ORDER BY` for predictable output ordering\n4. Combine with aggregate functions: `avg`, `sum`, `count`, `min`, `max`, `stddev`\n5. `time_bucket_gapfill()` 必须与 `GROUP BY` 配合使用\n6. `time_bucket_gapfill()` 可与 `interpolate()` 配合填充数据列的空值\n\nFile v1.2.1:references/ts-functions.md\n\n# KWDB Functions Quick Reference\n\nFunction syntax reference. For query scenarios and routing, see `scenarios.md`.\n\n## Time Functions\n\n| Function | Syntax | Description |\n|----------|--------|-------------|\n| `time_bucket` | `time_bucket(ts, 'interval')` | Align timestamps to fixed intervals |\n| `time_bucket_gapfill` | `time_bucket_gapfill(ts, 'interval')` | Align timestamps and fill gaps |\n| `date_trunc` | `date_trunc('precision', ts)` | Truncate timestamp to precision |\n| `now` | `now()` | Current timestamp (returns TIMESTAMPTZ) |\n| `age` | `age(end, begin)` | Calculate time interval between timestamps |\n| `to_timestamp` | `to_timestamp(val)` | Convert Unix epoch to timestamp |\n| `experimental_strftime` | `experimental_strftime(ts, format)` | Format timestamp using strftime |\n\n### date_trunc precision values\n`millennium`, `century`, `decade`, `year`, `quarter`, `month`, `week`, `day`, `hour`, `minute`, `second`, `millisecond`, `microsecond`\n\n### Additional timestamp functions\n| Function | Syntax | Description |\n|----------|--------|-------------|\n| `current_timestamp` | `current_timestamp()` | Current transaction timestamp |\n| `localtimestamp` | `localtimestamp()` | Current transaction timestamp (local) |\n| `statement_timestamp` | `statement_timestamp()` | Current statement start time |\n| `transaction_timestamp` | `transaction_timestamp()` | Current transaction time |\n| `timeofday` | `timeofday()` | Current system time (string) |\n\n## Time Intervals\n\nUsed with `time_bucket` and `time_bucket_gapfill`:\n\n| Unit | Keyword | Example |\n|------|---------|---------|\n| Nanosecond | `ns`, `nsec`, `nanosecond` | `'500ns'` |\n| Microsecond | `us`, `usec`, `microsecond` | `'100us'` |\n| Millisecond | `ms`, `msec`, `millisecond` | `'500ms'` |\n| Second | `s`, `sec`, `second` | `'30s'` |\n| Minute | `m`, `min`, `minute` | `'5m'` |\n| Hour | `h`, `hr`, `hour` | `'1h'` |\n| Day | `d`, `day` | `'7d'` |\n| Week | `w`, `week` | `'2w'` |\n| Month | `mon`, `month` | `'3mon'` |\n| Year | `y`, `yr`, `year` | `'1y'` |\n\n## Aggregation Functions\n\n| Function | Syntax | Description |\n|----------|--------|-------------|\n| `avg` | `avg(val)` | Average |\n| `sum` | `sum(val)` | Sum |\n| `count` | `count(*)` or `count(val)` | Count |\n| `min` | `min(val)` | Minimum |\n| `max` | `max(val)` | Maximum |\n| `stddev` | `stddev(val)` | Standard deviation (N denominator for population, N-1 for sample) |\n| `variance` | `variance(val)` | Population variance (stddev squared) |\n\nSupported input types: `INT2`, `INT4`, `INT8`, `FLOAT4`, `FLOAT8`, `DECIMAL`\n\n## First/Last Functions\n\n| Function | Syntax | Description |\n|----------|--------|-------------|\n| `first` | `first(val)` | First non-null value by timestamp |\n| `last` | `last(val)` | Last non-null value by timestamp |\n| `first_row` | `first_row(val)` | First value including nulls |\n| `last_row` | `last_row(val)` | Last value including nulls |\n\n## Interpolation\n\n| Function | Syntax | Description |\n|----------|--------|-------------|\n| `interpolate` | `interpolate(agg_func, mode)` | Fill missing values. Modes: `PREV`, `NEXT`, `'linear'`, `'constant'`, `NULL` |\n\nMust be used with `time_bucket_gapfill()`. The `method` parameter must be an aggregate function with numeric data type.\n\n## Time-Series Analysis\n\n| Function | Syntax | Description |\n|----------|--------|-------------|\n| `TWA` | `TWA(ts, expr)` | Time-weighted average |\n| `diff` | `diff(col) OVER (...)` | Difference from previous row |\n| `ELAPSED` | `ELAPSED(ts [, unit])` | Time coverage in units |\n\n## Window Functions\n\n| Function | Syntax | Description |\n|----------|--------|-------------|\n| `TIME_WINDOW` | `TIME_WINDOW(ts, 'interval' [, 'slide'])` | Sliding time windows |\n| `COUNT_WINDOW` | `COUNT_WINDOW(n [, slide])` | Fixed row count windows |\n| `SESSION_WINDOW` | `SESSION_WINDOW(ts, 'interval')` | Session-based windows (time gaps) |\n| `EVENT_WINDOW` | `EVENT_WINDOW(start_cond, end_cond)` | Event-based windows |\n| `STATE_WINDOW` | `STATE_WINDOW(col)` | State-change windows |\n\n## Date/Time Extraction\n\n| Function | Syntax | Description |\n|----------|--------|-------------|\n| `extract` | `EXTRACT(field FROM ts)` | Extract timestamp part |\n| `date_part` | `date_part('field', ts)` | Alternative extraction |\n\n### extract/date_part fields\n`year`, `month`, `day`, `hour`, `minute`, `second`, `epoch`, `millennium`, `century`, `decade`, `quarter`, `week`, `isoyear`, `dayofweek`, `isodow`, `dayofyear`, `julian`, `millisecond`, `microsecond`, `timezone`, `timezone_hour`, `timezone_minute`\n\n## Math Functions\n\n| Function | Syntax | Description |\n|----------|--------|-------------|\n| `abs` | `abs(val)` | Absolute value |\n| `round` | `round(val)` | Round to nearest |\n| `floor` | `floor(val)` | Round down |\n| `ceil` | `ceil(val)` | Round up (alias: `ceiling`) |\n| `sqrt` | `sqrt(val)` | Square root |\n| `cbrt` | `cbrt(val)` | Cube root |\n| `power` | `power(x, y)` | x to the power of y (alias: `pow`) |\n| `exp` | `exp(val)` | e raised to the power of val |\n| `log` | `log(val)` or `log(val, base)` | Logarithm (default base 10) |\n| `ln` | `ln(val)` | Natural logarithm |\n| `mod` | `mod(x, y)` | Modulo (remainder) |\n| `div` | `div(x, y)` | Integer division |\n| `sign` | `sign(val)` | Sign of value (-1, 0, 1) |\n| `trunc` | `trunc(val)` | Truncate decimal |\n| `pi` | `pi()` | Pi constant (~3.14159) |\n| `random` | `random()` | Random float between 0 and 1 |\n| `isnan` | `isnan(val)` | Check if value is NaN |\n\n### Trigonometric Functions\n\n| Function | Syntax | Description |\n|----------|--------|-------------|\n| `sin` | `sin(val)` | Sine (radians) |\n| `cos` | `cos(val)` | Cosine (radians) |\n| `tan` | `tan(val)` | Tangent (radians) |\n| `cot` | `cot(val)` | Cotangent (radians) |\n| `asin` | `asin(val)` | Inverse sine |\n| `acos` | `acos(val)` | Inverse cosine |\n| `atan` | `atan(val)` | Inverse tangent |\n| `atan2` | `atan2(y, x)` | Inverse tangent of y/x |\n| `degrees` | `degrees(val)` | Convert radians to degrees |\n| `radians` | `radians(val)` | Convert degrees to radians |\n\n### Hash Functions\n\n| Function | Syntax | Description |\n|----------|--------|-------------|\n| `crc32c` | `crc32c(val)` | CRC32C checksum |\n| `crc32ieee` | `crc32ieee(val)` | CRC32 IEEE checksum |\n| `fnv32` | `fnv32(val)` | FNV-32 hash |\n| `fnv32a` | `fnv32a(val)` | FNV-32a hash |\n| `fnv64` | `fnv64(val)` | FNV-64 hash |\n| `fnv64a` | `fnv64a(val)` | FNV-64a hash |\n\n### width_bucket\n\n| Function | Syntax | Description |\n|----------|--------|-------------|\n| `width_bucket` | `width_bucket(operand, b1, b2, count)` | Return bucket number of operand in histogram with count buckets |\n\n## String Functions\n\n| Function | Syntax | Description |\n|----------|--------|-------------|\n| `lower` | `lower(str)` | Convert to lowercase |\n| `upper` | `upper(str)` | Convert to uppercase |\n| `substring` | `substring(str, start [, len])` | Extract substring (aliases: `substr`) |\n| `length` | `length(str)` | String length |\n| `char_length` | `char_length(str)` | Character count (alias: `character_length`) |\n| `bit_length` | `bit_length(str)` | Bit length |\n| `octet_length` | `octet_length(str)` | Byte length |\n| `trim` | `trim(str)` | Remove leading/trailing whitespace |\n| `ltrim` | `ltrim(str)` | Remove leading whitespace |\n| `rtrim` | `rtrim(str)` | Remove trailing whitespace |\n| `concat` | `concat(str1, str2 [, ...])` | Concatenate strings (variadic) |\n| `initcap` | `initcap(str)` | Capitalize first letter of each word |\n| `left` | `left(str, n)` | First n characters |\n| `right` | `right(str, n)` | Last n characters |\n| `lpad` | `lpad(str, len [, fill])` | Left pad with spaces or fill |\n| `rpad` | `rpad(str, len [, fill])` | Right pad with spaces or fill |\n| `chr` | `chr(val)` | Character from ASCII code |\n| `strpos` | `strpos(str, substr)` | Position of substring |\n| `overlay` | `overlay(str1 PLACING str2 FROM pos FOR len)` | Replace substring |\n\n## Conditional Functions\n\n| Function | Syntax | Description |\n|----------|--------|-------------|\n| `COALESCE` | `COALESCE(val1, val2 [, ...])` | First non-null value |\n| `NULLIF` | `NULLIF(val1, val2)` | NULL if val1 equals val2 |\n| `IFNULL` | `IFNULL(val1, val2)` | val1 if not null, else val2 |\n| `CASE WHEN` | `CASE WHEN cond THEN val1 ELSE val2 END` | Conditional expression |\n\n## Geographic/Spatial Functions\n\n| Function | Syntax | Description |\n|----------|--------|-------------|\n| `ST_Distance` | `ST_Distance(a, b)` | Euclidean distance between two points |\n| `ST_DWithin` | `ST_DWithin(a, b, d)` | Check if distance between a and b is within d |\n| `ST_Contains` | `ST_Contains(a, b)` | Check if geometry a contains geometry b |\n| `ST_Intersects` | `ST_Intersects(a, b)` | Check if two geometries intersect |\n| `ST_Equals` | `ST_Equals(a, b)` | Check if two geometries are equal |\n| `ST_Touches` | `ST_Touches(a, b)` | Check if two geometries touch |\n| `ST_Covers` | `ST_Covers(a, b)` | Check if geometry a covers geometry b |\n| `ST_Area` | `ST_Area(geom)` | Calculate area of a polygon |\n\n## Type Casting\n\nUse `::type` for casting:\n\n```sql\n-- Cast to integer\nvalue::INT\nvalue::INT4\nvalue::INT8\n\n-- Cast to float\nvalue::FLOAT4\nvalue::FLOAT8\nvalue::DOUBLE\n\n-- Cast to string\nvalue::VARCHAR\nvalue::STRING\nvalue::CHAR\n\n-- Cast to timestamp\nvalue::TIMESTAMP\nvalue::TIMESTAMPTZ\n\n-- Cast to boolean\nvalue::BOOL\n\n-- Cast to date\nvalue::DATE\n```\n\n**Note on timestamp casting**: When the timestamp column in a time-series table is set to TIMESTAMP type, the system automatically converts it to TIMESTAMPTZ. Casting operations on this column will be processed according to the database timezone setting.\n\n**Common patterns:**\n```sql\n-- String to timestamp\n'2024-01-15'::TIMESTAMP\n\n-- Integer to timestamp (Unix epoch)\n1705315200::TIMESTAMP\n\n-- Timestamp to date\nts::DATE\n\n-- Keep only date part\ndate_trunc('day', ts)\n```\n\n## Special SQL Syntax Forms\n\nCompatible SQL standard syntax that KWDB supports:\n\n| Special Form | Equivalent To | Description |\n|--------------|---------------|-------------|\n| `AT TIME ZONE` | `timezone()` | Timezone conversion |\n| `CURRENT_DATE` | `current_date()` | Current date |\n| `CURRENT_TIME` | `current_time()` | Current time |\n| `CURRENT_TIMESTAMP` | `current_timestamp()` | Current timestamp |\n| `CURRENT_ROLE` / `CURRENT_USER` | `current_user()` | Current user |\n| `CURRENT_SCHEMA` | `current_schema()` | Current schema |\n| `SESSION_USER` | `current_user()` | Session user |\n| `USER` | `current_user()` | Current user (abbreviated) |\n| `CURRENT_CATALOG` | `current_database()` | Current database |\n| `EXTRACT(field FROM ts)` | `extract(field, ts)` | Extract timestamp part |\n| `EXTRACT_DURATION(field FROM val)` | `extract_duration(field, val)` | Extract duration part |\n| `OVERLAY(str1 PLACING str2 FROM pos FOR len)` | `overlay(str1, str2, pos, len)` | Replace substring |\n| `POSITION(substr IN str)` | `strpos(str, substr)` | Position of substring |\n| `SUBSTRING(str FOR len)` | `substring(str, 1, len)` | Substring from start |\n| `TRIM(chars FROM str)` | `btrim(str, chars)` | Trim characters |\n| `TRIM(LEADING chars FROM str)` | `ltrim(str, chars)` | Trim leading characters |\n| `TRIM(TRAILING chars FROM str)` | `rtrim(str, chars)` | Trim trailing characters |\n| `COLLATION FOR` | `pg_collation_for()` | Collation for expression |\n\n## Common Pitfalls\n\n1. **SUM overflow**: Avoid letting SUM results exceed the maximum supported range\n2. **Avoid escape character `+` in SUBSTRING regex**: Use `substr()` or `substring()` without regex patterns containing `+`\n3. **time_bucket interval format**: Do NOT use compound interval format like `'1d1h'`\n4. **NULL handling**: `last()` ignores NULLs, `last_row()` includes NULLs — choose based on data characteristics\n5. **Trig functions use radians**: `sin()`, `cos()`, `tan()` etc. expect values in radians, not degrees\n\nFile v1.2.1:references/ts-interpolation.md\n\n# Time Series Interpolation\n\nFill missing values in time-series data using `time_bucket_gapfill()` and `interpolate()`.\n\n## When to Use\n\nUse for: \"填充缺失值\", \"线性插值\", \"前值填充\", \"补全数据\"\n\n## time_bucket_gapfill + interpolate\n\n```sql\ntime_bucket_gapfill(timestamp_column, 'interval')\ninterpolate(aggregate_function, mode)\n```\n\n## Interpolation Modes\n\n| Mode | Description |\n|------|-------------|\n| `PREV` | Use previous value |\n| `NEXT` | Use next value |\n| `'linear'` | Linear interpolation |\n| `'constant'` | Use a constant value |\n| `NULL` | Fill with NULL (no fill) |\n\n## Examples\n\n**Linear Interpolation:**\n```sql\nSELECT time_bucket_gapfill(ts, '1h') AS hour,\n interpolate(avg(temperature), 'linear') AS temp\nFROM sensor_data\nWHERE ts >= NOW() - INTERVAL '1 day'\nGROUP BY hour\nORDER BY hour;\n```\n\n**Previous Value Fill:**\n```sql\nSELECT time_bucket_gapfill(ts, '30m') AS bucket,\n interpolate(avg(pressure), PREV) AS pressure\nFROM readings\nWHERE ts >= '2024-01-15' AND ts < '2024-01-16'\nGROUP BY bucket\nORDER BY bucket;\n```\n\n**Constant Fill:**\n```sql\nSELECT time_bucket_gapfill(ts, '1h') AS hour,\n interpolate(avg(value), '0') AS value\nFROM metrics\nWHERE ts >= NOW() - INTERVAL '7 days'\nGROUP BY hour\nORDER BY hour;\n```\n\n## Template\n\n```sql\nSELECT\n time_bucket_gapfill(ts, '') AS bucket,\n interpolate((), '') AS _filled\nFROM \nWHERE ts >= '' AND ts < ''\nGROUP BY bucket\nORDER BY bucket;\n```\n\n## Notes\n\n1. Must be used with `GROUP BY` and aggregate functions\n2. `interpolate()` requires an aggregate function as first parameter\n3. `time_bucket_gapfill()` creates buckets even where no data exists\n4. Choose mode based on data characteristics:\n - `linear` — continuous data (temperature, pressure)\n - `PREV` — step-like data (on/off states)\n - `'constant'` — fill with known default value\n\nFile v1.2.1:references/ts-latest-value.md\n\n# Time Series Latest Value\n\nRetrieve the most recent or oldest data points from time-series tables.\n\n## When to Use\n\nUse for: \"最新温度\", \"最近一条记录\", \"当前状态\", \"每个设备的最新读数\"\n\n## Functions\n\n```sql\nfirst(column) -- value at minimum timestamp (excludes NULL)\nlast(column) -- value at maximum timestamp (excludes NULL)\nlast_row(column) -- value at maximum timestamp (includes NULL)\nfirst_row(column) -- value at minimum timestamp (includes NULL)\n```\n\n**Note**: `last()` ignores NULLs; use `last_row()` to include NULLs.\n\n## Examples\n\n**Latest Value per Device:**\n```sql\nSELECT device_id,\n last(temperature) AS latest_temp,\n last(ts) AS timestamp\nFROM sensor_data\nGROUP BY device_id;\n```\n\n**Latest Values with Time Range:**\n```sql\nSELECT device_id,\n last(temperature) AS temp,\n last(humidity) AS humidity\nFROM sensor_data\nWHERE ts >= NOW() - INTERVAL '1 hour'\nGROUP BY device_id;\n```\n\n**Latest Row with NULLs:**\n```sql\nSELECT device_id,\n last_row(temperature) AS latest_temp\nFROM sensor_data\nGROUP BY device_id;\n```\n\n**Multiple Metrics:**\n```sql\nSELECT device_id,\n last(temperature) AS latest_temp,\n last(humidity) AS latest_humidity,\n last(ts) AS last_update\nFROM sensor_data\nGROUP BY device_id;\n```\n\n## Template\n\n```sql\nSELECT\n ,\n last() AS latest_,\n last(ts) AS timestamp\nFROM \n[WHERE ts >= '