43 honeydew-ai

filtering Skill

当用户需要筛选数据时使用——无论是结构化查询、指标聚合还是属性表达式。涵盖筛选语法、日期处理和最佳实践。

安装方式:把技能目录放入 ~/.claude/skills/(Claude Code)或在 claude.ai 设置中启用;也可复制右侧安装命令一键添加。

查看源码

技能指令原文(SKILL.md)

Overview

Filtering restricts which rows contribute to a result. The same expression language applies across three contexts in Honeydew:

| Context | Where it appears | When it runs |
| ------------------------ | --------------------------------------------------------- | ------------------ |
| Structured query | filters parameter in get_data_from_fields / get_sql_from_fields | Pre-aggregation |
| Metric aggregation | FILTER (WHERE ...) on an aggregation | During aggregation |
| Attribute expression | CASE WHEN ... END in attribute SQL | Per-row evaluation |
| Metric value filter | Metric expression in filters parameter | Post-aggregation |


Filter Expression Syntax

Comparisons

entity.field = 'value'
entity.field > 100
entity.field >= 3 AND entity.field < 10

Operators: =, <, >, >=, <=, !=

You can compare an attribute to a constant or to another attribute. Cast mismatching types when comparing (e.g., entity.field::DATE).

Strings

entity.field = 'exact value'
entity.field IN ('val1', 'val2', 'val3')
entity.field ILIKE '%keyword%'
  • ILIKE is case-insensitive pattern matching (% = any characters, _ = one character)

Full-Text Search (Snowflake only)

Use SEARCH when you don't know exact values and need to find possible matches.

-- Single search — always use SEARCH_MODE => 'AND'
SEARCH(entity.field, 'search terms', SEARCH_MODE => 'AND')

-- Multiple alternatives — use OR between SEARCH calls
SEARCH(entity.field, 'term1', SEARCH_MODE => 'AND') OR SEARCH(entity.field, 'term2', SEARCH_MODE => 'AND')

Always use SEARCH_MODE => 'AND'.

NULL Checks

entity.field IS NULL
entity.field IS NOT NULL

Booleans

entity.flag = true
entity.flag = false

Date Comparisons

YEAR(entity.date_field) = 2023
entity.date_field >= '2024-01-01'
entity.date_field BETWEEN '2024-02-05'::DATE AND '2024-02-10'::DATE

Combining Conditions

entity.price > 50 AND entity.room_type = 'Private room'
entity.status = 'active' OR entity.status = 'pending'

Use AND / OR to combine conditions. Use parentheses to control precedence.

Type Casting

Cast when types don't match:

entity.string_field::DATE
entity.number_field::VARCHAR
'2024-01-01'::DATE
DATE('2024-01-01')

Filtering Contexts

1. Structured Query Filters

Pass filters as a list of expressions in the filters parameter of get_data_from_fields or get_sql_from_fields. Filters are applied before aggregation (equivalent to SQL WHERE):

Call get_data_from_fields with:

  • attributes: ["detailed_listings.neighbourhood_cleansed"]
  • metrics: ["detailed_listings.count"]
  • filters: ["detailed_listings.room_type = 'Entire home/apt'", "detailed_listings.price > 50"]

Each entry in the filters list is ANDed together.

2. Metric Aggregation Filters

Inside a metric's SQL, use FILTER (WHERE ...) to restrict which rows feed the aggregation:

SUM(orders.price) FILTER (WHERE orders.color = 'red')
COUNT(orders.id) FILTER (WHERE orders.status = 'completed')

Use FILTER (WHERE ...), not CASE WHEN, for filtered aggregations in metrics.

3. Attribute Expression Filters

In attribute SQL, use CASE WHEN for conditional per-row logic:

CASE
  WHEN orders.amount > 1000 THEN 'high'
  WHEN orders.amount > 100 THEN 'medium'
  ELSE 'low'
END

4. Filtering by Metric Values

You can filter on aggregated metric values — the equivalent of SQL's HAVING clause. Use the metric expression (named or ad-hoc) in the filters parameter of a structured query. These filters are applied after aggregation.

This works with both named metrics (e.g., entity.metric_name > 10) and ad-hoc aggregations (e.g., COUNT(entity.field) > 1).

For examples — including duplicate detection, minimum group size, and revenue thresholds — see examples.md.

Date Handling

Date Functions

| Function | Use |
| -------------- | ---------------------------------------------------- |
| CURRENT_DATE | Reference today |
| DATE_TRUNC | Get boundaries: DATE_TRUNC(month, CURRENT_DATE()) |
| INTERVAL | Relative time: CURRENT_DATE() - INTERVAL '1 month' |
| Cast strings | DATE('2024-01-01') or '2024-01-01'::DATE |

Example — Last Month Filter

DATE_TRUNC(month, order.order_date) = DATE_TRUNC(month, CURRENT_DATE() - INTERVAL '1 month')
Do NOT use interval calculation when asked about specific dates (e.g., "November 2024"). Use explicit date values instead.

Best Practices

  • Discover available values if needed — if you're unsure what values a field contains, check its distinct values by querying it as an attribute with a COUNT metric (see the query skill's "Getting Distinct Values" tip)
  • Use SEARCH when values are unknown — avoids hard-coding exact strings
  • Use ILIKE for pattern matching — case-insensitive, good for partial matches
  • Cast types explicitly — prevents silent type coercion errors
  • Use FILTER (WHERE ...) in metrics, not CASE WHEN — cleaner, standard SQL
  • Use CASE WHEN in attributes — for per-row conditional logic
  • Prefer IN (...) over multiple OR — cleaner for known value lists
  • Use date functions for relative dates — CURRENT_DATE, DATE_TRUNC, INTERVAL
  • Use explicit dates for specific periods — don't compute "November 2024" via interval math

配合站内内容