Snowflake group by all columns

Snowflake group by all columns. ${. If one of the arguments is a number, the function coerces non-numeric string arguments (e. See also: group by rollup¶. For each column, if there is no value to return, the subquery returns NULL. Individual columns in a table, using the CREATE TABLE command. SELECT DISTINCT A,B. Schema for the table. ANY_VALUE() is a non deterministic function, so if something like a BI dashboard runs the query periodically between data changes on the tables, Snowflake will not be able to use the result set cache and have to run the query again. [FirstName], Pro. Usage notes¶ Snowflake allows up to 128 grouping sets in the same query block. Default value of the column. Jan 22, 2023. ” OR “GROUP BY 1,2,3,4”. position. Any expression on tables in the current scope. Because a bitmap can represent at most 32,768 distinct values, this approach requires “bucketizing” (using multiple bitmaps) if the number of distinct values exceeds 32,768. The session parameter WEEK_OF_YEAR_POLICY is set to indicate that the first week of the year is the week that contains January 1st of that year. Rotates a table by transforming columns into rows. The list includes: The name of each function. Mar 1, 2024 · select <all_Columns> from (select t. Apr 17, 2019 · While the previous statement is true about Snowflake, actually this option, when set to true, will add an identity column to the target table, which in this case I don’t need. More precisely, a window function is passed 0 or more expressions. All columns added to a table, using the ALTER TABLE command. An alternative approach would be to add the 'Count' column using transform and then call drop_duplicates: In [25]: df['Count'] = df. 123-07:00 If possible, pass in arguments of the same type. GROUP BY ALL: In most of the cases where there is a need to do aggregation on multiple columns we tend to write GROUP BY with clauses like “GROUP BY C1, C2, C3, etc. PIVOT function in Snowflake does require an explicit list of columns, however quite often a business case requires this list to be obtained dynamically. id , ANY_VALUE(customer. Group by all columns. Example 3: Using GROUP BY ROLLUP to Roll up Counts by Group. Super nice! it even works group_by(across(!hp). Chonchi-in Tokyo, Japan Jan 21, 2023. Note the following: Snowflake dynamically rewrites the query applying the masking policy SQL expression to the column. FROM cte. table_name. Doing something like a lateral flatten gives me the correct output array but the sum is off because the flattens array created extra rows with qty. [LastName] Add some aggregate function on the relevant The following demonstrates the use of the functions WEEK, WEEKISO, WEEKOFYEAR, YEAROFWEEK, and YEAROFWEEKISO. GROUP BY keys that are not within the SELECT list. groupby(['Name'])['ID']. Name of the table the columns belong to. group by 1 --grouped by my_first_field. SELECT row_number() over (order by seq8(1)) as unique_id Feb 28, 2023 · 1. it is filtered out). Jun 29, 2023 · Simply put, Snowflake GROUP BY ALL provides a shortcut which detects all non-aggregated expressions in the SELECT clause, and replaces ALL with these expressions. built-in) functions, scalar or table, in alphabetical order. PT to learn how to build a data application leveraging Snowflake Marketplace, Snowpark and Streamlit. Join our community of data professionals to learn, connect, share and innovate together SELECT. SUM(CASE WHEN account = '31' THEN value ELSE 0 END) AS Total_SUM. Apr 28, 2020 · What I'm trying to accomplish is to get all the records for a given MPN, however, I only want the latest DeliveryDate from shpm but given the fact that the MAX function needs to be in the group by MIN_BY. This file format option is applied to the following actions only when loading Orc data into separate columns using the MATCH_BY_COLUMN_NAME copy option. This method returns a RelationalGroupedDataFrame that you can use to perform aggregations on each A CTE (common table expression) is a named subquery defined in a WITH clause. Understanding DATE_TRUNC. a SELECT statement). These three column lists must all correspond to each other. The columns are returned in the following order: The columns in the USING clause in the order specified. On a year-month basis (yyyy-mm) I need to calculate the sum total grossamount of all orders and divide this number by the total count of (distinct) account ids. 1. distinct is metadata level information in snowflake and it is fast. Jul 15, 2012 · SELECT DISTINCT COUNT (DISTINCT t1. Calculate the average of the columns that are numeric or that can be converted to numbers May 23, 2024 · The GROUP BY statement appears at the end of a query, after any joins and WHERE filters have been applied: select. External Tokenization enables accounts to tokenize data before loading it into Snowflake and detokenize the data at query runtime. Sequences are used to generate unique numbers across sessions and statements, including concurrent statements. The first step in performing a `GROUP BY` operation by date is to understand how the `DATE_TRUNC` function works. projections, join predicate, where clause predicate, order by, and group by). col1, col2, col3, col4. This function performs various group by pandas operations. my_first_field, count(id) as cnt --or any other aggregate function (sum, avg, etc. snowflake. GROUP BY CUBE is an extension of the GROUP BY clause similar to GROUP BY ROLLUP. UNPIVOT (NULLS_COLUMN_COUNT FOR COLUMN_NAME IN (<column_list>)) ORDER BY COLUMN_NAME; Now using the metadata and variables: -- input. For numeric string arguments that are not constants, if NUMBER (18,5) is A non-scalar subquery returns 0, 1, or multiple rows, each of which may contain 1 or multiple columns. Here the delete with inner or left join wont work instead you have to use USING clause. A specific comparison within a SQL statement (e. I would expect that tuples where at least one of the members is not NULL, would be included in the count. SET sch_name = 'my_schema'; SET tab_name = 't'; SELECT. This method returns a RelationalGroupedDataFrame that you can use to perform aggregations on each . Select L_QUANTITY and add the aggregation below to the Transform field: sum({{SRC}}) In your Config panel to the right, click on General Options. Also, after this step, I want to apply pandas. pivot_table function? can anybody plz suggest or share docs? Thank you, team. You cannot (should not) put non-aggregates in the SELECT line of a GROUP BY query. GROUPING SETS is as powerful feature which provides unified result set with the aggregated data for all grouping sets (i. schema_name. ' || table_name || ' RENAME COLUMN ' || column_name || ' TO _LA' || column_name from information_schema. Inserts, updates, and deletes values in a table based on values in a second table or a subquery. SELECT. *, row_number() over (partition by <all_columns> order by project_id) as rn from your_table t) t where rn = 1; row_number will always gives the unique row number to the partitioned records even if order by columns have same values. Avoid passing in arguments of different types. Here's how: 1. You can, and have to, define what you are grouping by for the aggregate function to return the correct result. GROUP BY CUBE. select. I've tried the following: SELECT TO_CHAR (DATE_TRUNC ('month', orderdate::date), 'yyyy-mm') AS year_month, SUM Feb 13, 2023 · SUM(CASE WHEN account = '31' THEN value ELSE 0 END) AS value2, SUM(CASE WHEN account = '30' THEN value ELSE 0 END) -. I want the value separate record-level values and then the sum of it and then I will snowflake. A list of the categories that the function belongs in. Let us understand with an example. In your case the 'Name', 'Type' and 'ID' cols match in values so we can groupby on these, call count and then reset_index. Maximum length in bytes of string columns. DataFrame. transform('count') Mar 9, 2023 · Relevant columns are accountid, orderdate (timestamp) and grossamount. table name: select 'ALTER TABLE ' || table_schema || '. order by 1 desc. Data type of the column. In almost all cases, at least one of those expressions references a column in that row. The intension using them is to get multiple group by results in one statement meaning Reference SQL Command Reference General DML MERGE MERGE¶. in snowflake below data is maintained by metadata layer (cloud service layer) hence it is fastest and also do not requires any active warehouse. You can use object_construct and array_agg within group to do this: ,array_agg(object_construct('value-1', "value-1", 'value-2', parse_json("value-2"))) within group (order by ID) as JSON_VALUE. With this approach, you use the bitmap functions to produce bitmaps that identify the distinct integer values in a column. Example 2: Using GROUP BY to Compute the Counts by Group. You can use the DISTINCT keyword to compute the sum of unique non-null values. select listagg($1,', ')from(. Imagine a scenario where grouping sets functionality is not Window function sub-clause that specifies an expression (typically a column name). ·. Select the C_NAME column and drag it to the top of your Mapping grid so that it appears in front of the L_QUANTITY column. (Most window functions require at least one column or In the standard JOIN syntax, the projection list (the list of columns and other expressions after the SELECT keyword) is “*”. value. Important. Finds the row (s) containing the minimum value for a column and returns the value of another column in that row. Window function sub-clause that specifies an expression (typically a column name). For example, UPDATE t1 SET t1. I'm trying to records in a table on a per week basis and using this link as a reference on how to group by time in Snowflake SQL: select COUNT(table_id), DATE_TRUNC('WEEK', DATEADD(Day, -49, CURRENT_DATE)) FROM data_warehouse_source. Regarding Snowflake’s index independence…data is stored in an optimized, proprietary file format, as micro partitions, that is automatically compressed (and encrypted). For example, the following Jun 25, 2014 · Some databases support GROUP BY ALL, such as Snowflake (see documentation here). from sessions. Sub-total rows are rows that further aggregate whose values are derived by computing the same aggregate functions that were used to state が GROUP BY で使用されている場合、Snowflakeはそれをエイリアスではなく列名への参照として解釈します。したがって、このクエリは、カリフォルニア州の従業員の給与合計とオレゴン州の従業員の給与合計を返しますが、employment_stateの情報（例 An ORDER BY can be used at different levels in a query, for example in a subquery or inside an OVER () subclause. Returns¶ Returns a string that includes all of the non-NULL input values, separated by the delimiter. The values in col2 are NULL. A window function is any function that operates over a window of rows. Note that because the number of values in group k = 2 is an even number, the returned value for that group is the mid-point between the two middle numbers. For example, the limit applies to the the number of expressions in the following SELECT statement: Mar 31, 2022 · Snowflake Count Group By With Condition. e columns used in grouping) SOLUTION:Why Grouping Sets? This feature simplifies the query writing for multiple group by with union all and is easier to understand. ‘YES’ if the column may contain NULL, ‘NO’ otherwise. snowpark. If all records inside a group are NULL, the function returns NULL. . Let's look at an example table: When queried it looks like follows: And we would expect something like this in return: The following examples demonstrate how to use the MIN function. Position of an expression in the SELECT list. I basically want to get the count of records for each of last say 7 weeks (hence 49 days above Jul 7, 2020 · My first attempt was to do something like. The CTE defines the temporary view’s name, an optional list of column names, and a query expression (i. Jan 29, 2018 · In SQL Server you can only select columns that are part of the GROUP BY clause, or aggregate functions on any of the other columns. The values in col1 are not NULL. If multiple collations are applied to a statement, Snowflake determines the collation to use based on precedence. GROUP BY ROLLUP. group-by. For example, if a single column in a table has 10 unique tags set on the column, Snowflake allows: Setting 40 additional unique tags on either that same column, other columns in the table, or some combination of the columns in the table. FROM CountTest; I would assume so, but apparently the first query filters out any rows that has NULL values. group by rollup （グループ化された行に加えて）小計行を生成する group by 句の拡張です。 小計行は、グループ化された行を生成するために使用されたのと同じ集計関数を計算することによって値が導出される、さらに集計される行です。 Mar 10, 2022 · I would like to do something like GROUP BY animal or PARTITION BY animal, and then generate all possible combinations for the key column inside the grouped animal col, because I don't wanna do unnecessary computations of combining keys for different animals. SELECT COUNT (1) FROM (. expr. Types Supported by Snowflake¶ Snowflake currently supports the following types of subqueries: This topic provides a list of all Snowflake system-defined (i. project_ID, project_name, employee_ID, employee_name, e May 21, 2024 · In Snowflake, you have the option of using the EXCLUDE keyword within your SELECT statement to give certain columns the slip, ensuring they stay out of your query results. In a query, it is specified in the FROM clause after the table name or subquery. For example, if a table contains the columns employee_id and salary, MAX_BY(employee_id, salary) returns the value of the employee_id column for the row that has the highest value in the salary column. The left table columns not specified in the USING UNPIVOT. Jan 6, 2023 · 0. in a subquery), but these three column lists must be present. You can reproduce the behavior with this table: Sep 1, 2021 · Since that's the case, using ANY_VALUE() will be faster for single queries but be careful of its effect on result set caching. All columns combined in a single table or view: 50 unique tags. Before: Nov 5, 2023 · GROUP BY ALL in Snowflake. id, SUM(qty), ARRAY_AGG(collection) GROUP BY id. group_by¶ DataFrame. Note that Snowflake converts all instances of the value to NULL, regardless of the data type. This query contains two ORDER BY sub-clauses, one to control the order of rows in each partition, and one to control the order of the output of the full query. For details, see Mar 6, 2024 · The ALL keyword can be used with the GROUP BY clause to retain all unique combinations of the grouped columns, including those with NULL values. The column rewrite occurs at every place where the column specified in the masking policy appears in the query (e. GROUP BY GROUPING SETS. arrays. GROUP BY 2. An ORDER BY inside a subquery or subclause applies only within that subquery or subclause. This method returns a RelationalGroupedDataFrame that you can use to perform aggregations on each This shows a simple query using FIRST_VALUE(). UNPIVOT is a relational operator that accepts two columns (from a table or subquery), along with a list of columns, and generates a row for each column specified in the list. TEXT for all character types, FIXED The following examples demonstrate how to use the aggregation functions that produce ARRAYs of distinct values as an alternative to COUNT(DISTINCT <expression>). Description. Finds the row (s) containing the maximum value for a column and returns the value of another column in that row. Here's what the implementation looks like Snowflake maintains statistics on tables and views, and this optimization allows simple queries to run faster. Thank you, yes i was thinking the same. 'a string') and string arguments that are not constants to the type NUMBER (18,5). For details, see Window Functions. Column alias appearing in the query block’s SELECT list. expr3 and expr4 specify the column (s) or A corollary of this feature is to be able to generate dynamically column names from functions, which also can't be done and would solve this problem. The function you need here is date_trunc(): -- returns number of sessions grouped by particular timestamp fragment select date_trunc('DAY',start_date), --or WEEK, MONTH, YEAR, etc count(id) as number_of_sessions. Mar 8, 2021 · SELECT COLUMN_NAME, NULLS_COLUMN_COUNT,SUM(NULLS_COLUMN_COUNT) OVER() AS NULLS_TOTAL_COUNT. g. Name of the column. This causes the query to return the key_column exactly once. Table or view that the column belongs to. If no rows qualify to be returned, the subquery returns 0 rows (not NULLs). However, group by with multiple columns gives null rows. m. Specifies that all items in the SELECT list that do not use aggregate functions should be used for grouping. This function allows you to specify the level of granularity for your date or timestamp column, such as minute, hour, day, week, etc. col = 1 is invalid. To group by all columns, simply pass all the columns as arguments to the groupBy() method. QUALIFY ROW_NUMBER() OVER (PARTITION BY col1, col2 ORDER BY col1, col2)=1. A summary of each function. If you want only a single group, then omit the PARTITION BY clause. column_name. You can think of the CTE as a temporary view for use in the statement that defines the CTE. This can be useful if the second table is a change log that contains new rows (to be inserted), modified rows (to be updated), and/or marked rows (to be deleted) in the target table. id = orders. Snowflake “group by <columns>” statement has extensions like “group by grouping sets < columns>”, “ group by rollup <columns>” and “group by cube <columns>”. Snowflake does not guarantee generating sequence numbers without gaps. Here's how you can obtain this result. name) , SUM(orders. Create a table and data: Display the data: Use the MAX function to retrieve the largest value in the column named d: Combine the GROUP BY clause with the MAX function to retrieve the largest values in each group (where each group is based on the value of column k ): cte_column_list. WHERE col1 = col2). Nesting of subqueries within a materialized view. When I run the query below, I get an array of arrays and some markets may be listed multiple times because the distinct clause is looking at the unique arrays and not the values in the arrays. 3 min read. Returns the sum of non-NULL records for expr. ALTER SESSION SET WEEK_OF_YEAR_POLICY = 1; SELECT '2016-01-02T23:39:20. The values in the Markets column are already in an array format. Execute queries in Snowflake. The next query contrasts the outputs of FIRST_VALUE, NTH_VALUE, and LAST_VALUE. For example, the ORDER BY in the following query orders results only within the subquery, not the outermost level of the query: The GROUP BY ALL is useful when you want to retain NULLs and include all unique combinations of grouped columns in the result set. Specifies the new value to set in col_name. PARTITION BY expr2. This method returns a RelationalGroupedDataFrame that you can use to perform aggregations on each Use a combination of IS NOT NULL and IS NULL to return the rows for which either of the following conditions is met:. Column. For example, suppose that you are selecting data across multiple states (or provinces) and you want row numbers from 1 to N within each state; in that case, you can partition by the state. Ask Question Asked 2 years, Using SQL count of multiple column values in Snowflake and transpose table output. The GROUP BY ALL clause is similar to the GROUP BY clause except that it automatically includes all non-aggregate items in the SELECT clause for grouping, eliminating the need to specifying them again for data grouping. If you want to return all columns except order_id, customer_id, and number_products , all you need to change from the previous example is to wrap the column names in brackets: select * exclude (order_id, customer_id, number_products) from orders; Which would return the following data Mar 27, 2024 · In Spark, selecting all columns of a DataFrame with groupBy can be achieved using the groupBy() and agg() and Join() methods. It's great to simplify some queries (especially during analysis or development, when the columns are likely to change often). Note that: Get the MEDIAN value for each group. Learn More >> BUILD DATA APPS Join this instructor-led, hands-on lab on May 14 at 1 p. Row Count in a table. Then, use the agg() method to apply aggregation functions to the remaining columns. Maximum length in characters of string columns. Here's an example: Let’s say you have a table GROUP BY CUBE. columns where table_schema ilike 'schema' -- put your schema name here and table_name ilike 'table' -- put your table name here order by ordinal_position; Instead you need to “truncate” your timestamp to the granularity you want, like minute, hour, day, week, etc. The following examples demonstrate how to use the MAX function. Note that the examples in each section use the data that you set up in Setting up the data for the examples. Obviously, this will create nulls due to duplicate column still being present after the first pivot. The MINUS, EXCEPT, or INTERSECT set operators. Demonstrate precedence when a column name and an alias match. The join operation specifies (explicitly or implicitly) how to relate rows in one table to the corresponding rows in the other table, typically by referencing the common column (s), such as project ID. with your first 2 sum statements you are summing all the values of value1. e. SELECT can be used as either a statement or as a clause within other statements: As a statement, the SELECT statement is the most commonly executed SQL statement; it queries the database and retrieves a set of rows. 0. To handle that, we can use max in the final select. See ORDER BY. Oct 12, 2022 · Create a dynamic PIVOT in Snowflake. Create a table and data: Display the data: Use the MIN function to retrieve the smallest value in the column named d: Combine the GROUP BY clause with the MIN function to retrieve the smallest values in each group (where each group is based on the value of column k ): Jan 22, 2023 · Towards Dev. They can be used to generate values for a primary key or any column that requires a unique value. Group by with single column works well. which gives me the correct qty sum but the array agg is multidimensional array. Specifies the name of a column in target_table. Jan 10, 2023 · Select all columns except two or multiple using exclude. Nov 19, 2023 · The `GROUP BY ALL` clause in Snowflake significantly saves time for data analysts and database users. The maximum number of expressions in a list is 16,384. External Tokenization. The result of the query expression is Aug 27, 2016 · This enables us to group by everything but one column ( hp in this example) by writing: You may even supply several columns to ignore: df %>% group_by_at(vars(-hp, -cyl)) without needing to use the c () construct. ) from my_table. A window function is generally passed two parameters: A row. Dynamic Data Masking is a Column-level Security feature that uses masking policies to selectively mask plain-text data in table and view columns at query time. so that you will not miss the actual users and their privileges of the table(if you drop and recreate then u need to The best solution I found for this is using the `QUALIFY` clause as such: SELECT. For example, if 2 is specified as a value, all instances of 2 as either a string or number are Nov 19, 2013 · One note: You can also have Constant columns that are not in the GROUP BY clause. id; Using Sequences. This trick comes in handy especially when you’re dealing with a large number of columns and only want to give the boot to a select few, making your query outcomes cleaner Dynamic Data Masking. customer_id GROUP BY customer. SELECT col1, col2, SUM(value) AS total_value FROM example_table MAX_BY. Many aggregate functions are not allowed in a materialized view snowflake. data_type. Setting up the data for the examples¶ The WITHIN GROUP(ORDER BY) syntax supports the same parameters as the main ORDER BY clause in a SELECT statement. value) FROM customer JOIN orders ON customer. Ordinal position of the column in the table. In a WHERE clause, if an expression evaluates to NULL, the row for that expression is removed from the result set (i. GROUP BY ALL. The following sections provide examples of using the GROUP BY clause: Group by one column. With ANY_VALUE, you can execute the following query: SELECT customer. Schema that the table belongs to. For example, the following joins the project and employee tables shown above: SELECT p. As a clause, SELECT defines the set of columns returned by a query. Optional parameters¶ FROM additional_tables. This simplifies the GROUP BY clause, but still requires computing the MIN function, which incurs an extra cost. So you have two options: Add the additional columns to the GROUP BY clause: GROUP BY Rls. anchor_column_list (in the anchor clause) recursive_column_list (in the recursive clause) A recursive CTE can contain other column lists (e. Reduces Manual Data Manipulation: Standard `GROUP BY` omits categories with no Jun 4, 2021 · 1. ; note that character and numeric columns display their generic data type rather than their defined data type (i. When a row access policy is set on a table or view and the COUNT function is used in a query, Snowflake must scan each row and determine whether the user is allowed to view the row. In pseudo-code, this looks similar to: JOIN A USER GROUP CHAPTER Located in cities around the world, our user groups bring together data professionals to connect, share ideas and innovate together. Do not include the table name. If all columns are duplicated in your table go with Distinct and load it in temp table and then truncate your actual table, and insert the records from temp table. All GROUP BY keys in a materialized view must be part of the SELECT list. where my_first_field is not null. Syntax would be less clean and efficient, but if we could dynamically generate column names, then we could also. For clarity, when I say constant I mean something like "Select 1 sort_order FROM table1" where you are assigning a constant Hello Team, I have a created a Python udtf on snowflake. MySQL (and SQLite) decided in their infinite wisdom that they would go against spec, and allow queries to accept GROUP BY clauses missing columns Sep 1, 2021 · If you wish to pivot period twice for sales and profit, you'll need to duplicate the column so you have one for each instance of pivot. Example 1: Counting the Distinct Values in a Single Table. FROM table. I am looking for option on how to see i can generate this without explicitly name each column. Group by multiple columns. SELECT DATE_TRUNC('month', transaction_date) AS month, SUM(amount) AS Oct 26, 2020 · this script produce alter command for all columns of given schema. groupBy¶ DataFrame. In addition to producing all the rows of a GROUP BY ROLLUP, GROUP BY CUBE adds all the “cross-tabulations” rows. Select Group By All and toggle this on to utilize Snowflake's GROUP BY ALL Window function sub-clause that specifies an expression (typically a column name). The output typically contains some NULL values. Your query contains GROUP BY twice, the list of columns to group by contains a column that is not present in the list of selected columns and most of the query is commented out. Join our community of data professionals to learn, connect, share and innovate together FROM CountTest; the same one as. But it is true all columns must be in one of three categories: An aggregate function, a constant, or it must appear in the GROUP BY clause. You can partition by 0, 1, or more expressions. I've blogged about this in detail here. Specifies one or more tables to use for selecting rows to update or for setting new values. As far as I know, this is equivalent to `SELECT DISTINCT ON (col1, col2) col1, col2, col3, col4 FROM table`. The following query fixes those 3 problems and might work, but we can't know without sample data. Would be great if this could be marked as the answer now. Column data type and applicable properties, such as length, precision, scale, nullable, etc. This expression defines partitions that group the input rows before the function is applied. 7. groupBy (* cols: Union [Column, str, Iterable [Union [Column, str]]]) → RelationalGroupedDataFrame [source] ¶ Groups rows by the columns specified by expressions (similar to GROUP BY in SQL). RoleName, Pro. To select all columns of the original column_alias. Few of the downside with this approach is think of it like if we want to “add” OR “remove” some of the columns in the SELECT clause then Aug 6, 2018 · Hello @Hanu (Navis) , . Column4) AS Column5, Column1, Column2, Column3, Column4 FROM TableA AS t1 GROUP BY Column1, Column2, Column3; This doesn't work: Msg 8120, Level 16, State 1, Procedure COUNT, Line 29 Column 'Column4' invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. group_by (* cols: Union [Column, str, Iterable [Union [Column, str]]]) → RelationalGroupedDataFrame [source] ¶ Groups rows by the columns specified by expressions (similar to GROUP BY in SQL). For example, if a table contains the columns employee_id and salary, MIN_BY(employee_id, salary) returns the value of the employee_id column for the row that has the lowest value in the salary column. go ly zx px xw ap uw my oy uy