Oh, interesting! This is an edge case I hadn't considered. I'm wondering how we could make the "unique" bar more generally selectable (not just for one value).

Right now the query for the "value counts" is a bit convoluted. I tried to push down the aggregation of uniqueness to duckdb so I'd return fewer entries to the client (e.g., in the case where the data is large and all values are unique).

Lines 59 to 84 in 3dbcc8b

    
           	override query(filter: Array<SQLExpression> = []): Query { 
        
           		let counts = Query 
        
           			.from({ source: this.#table }) 
        
           			.select({ 
        
           				value: sql`CASE 
        
           					WHEN ${column(this.#column)} IS NULL THEN '__quak_null__' 
        
           					ELSE ${column(this.#column)} 
        
           				END`, 
        
           				count: count(), 
        
           			}) 
        
           			.groupby("value") 
        
           			.where(filter); 
        
           		return Query 
        
           			.with({ counts }) 
        
           			.select( 
        
           				{ 
        
           					key: sql`CASE 
        
           						WHEN "count" = 1 AND "value" != '__quak_null__' THEN '__quak_unique__' 
        
           						ELSE "value" 
        
           					END`, 
        
           					total: sum("count"), 
        
           				}, 
        
           			) 
        
           			.from("counts") 
        
           			.groupby("key"); 
        
           	}

So right now, the table I get back from duckdb looks like:

[{ "key": "A", "total": 500 }, { "key": "C", "total" 20" }, { "key": "__quak_unique__", "total": 1 }]

This structure means the client doesn't have direct access to the actual unique values.

I’m wondering whether we could rethink the approach to selecting unique values, making them more broadly selectable rather than limited to just a single unique value. For instance, we could use a predicate to exclude values like "A" and "C," allowing the "unique" bar to represent a broader, more flexible selection. Curious what you think.

	override query(filter: Array<SQLExpression> = []): Query {
	let counts = Query
	.from({ source: this.#table })
	.select({
	value: sql`CASE
	WHEN ${column(this.#column)} IS NULL THEN '__quak_null__'
	ELSE ${column(this.#column)}
	END`,
	count: count(),
	})
	.groupby("value")
	.where(filter);
	return Query
	.with({ counts })
	.select(
	{
	key: sql`CASE
	WHEN "count" = 1 AND "value" != '__quak_null__' THEN '__quak_unique__'
	ELSE "value"
	END`,
	total: sum("count"),
	},
	)
	.from("counts")
	.groupby("key");
	}

Filter by single value does not work #67

Description

Metadata

Metadata

Assignees

Labels

Projects

Milestone

Relationships

Development

Issue actions