Trying to sample specific rows in Snowflake-CodePudding

I'm attempting to Sample specific a percentage or a whole number of products in specific rows in Snowflake, but unsure of which Syntax to use.

I normally work in Teradata, so using this particular syntax in Snowflake is a challenge to discover.

This is how I would normally sample my data in Teradata

```

```
SAMPLE
    WHEN Products = 'Product 1' THEN 150
    WHEN Products = 'Product 2' THEN 50
    WHEN Products = 'Product 2' THEN 50
    END
```

```

In Teradata, I run this block after my WHERE clause and it gives me the amount of rows I need for that particular product.

Is there an equivalent I can use in Snowflake?

I tried using "SAMPLE" within the select statement in my Snowflake query, but I'm unable to figure out how to request a specific product title and not just the number of rows.

CodePudding user response：

Such behaviour could be emulated using QUALIFY and ROW_NUMBER sorted by random value:

SELECT *
FROM tab_name
QUALIFY ROW_NUMBER() OVER(PARTITION BY Products ORDER BY RANDOM()) <=
       CASE 
        WHEN Products = 'Product 1' THEN 150
        WHEN Products = 'Product 2' THEN 50
        WHEN Products = 'Product 3' THEN 50
       END

Demo:

CASE expression could be replaced with DECODE: DECODE(Products,'Product 1',3,'Product 2',2,'Product 3',1)