What’s a good way to check if two datetimes are on the same calendar day in TSQL?
This is much more concise: where datediff(day, date1, date2) = 0
user-defined-functions
Deterministic function in mysql
From the MySQL 5.0 Reference: Assessment of the nature of a routine is based on the “honesty” of the creator: MySQL does not check that a routine declared DETERMINISTIC is free of statements that produce nondeterministic results. However, misdeclaring a routine might affect results or affect performance. Declaring a nondeterministic routine as DETERMINISTIC might lead … Read more
How to define and use a User-Defined Aggregate Function in Spark SQL?
Supported methods Spark >= 3.0 Scala UserDefinedAggregateFunction is being deprecated (SPARK-30423 Deprecate UserDefinedAggregateFunction) in favor of registered Aggregator. Spark >= 2.3 Vectorized udf (Python only): from pyspark.sql.functions import pandas_udf from pyspark.sql.functions import PandasUDFType from pyspark.sql.types import * import pandas as pd df = sc.parallelize([ (“a”, 0), (“a”, 1), (“b”, 30), (“b”, -50) ]).toDF([“group”, “power”]) def … Read more
COLLECT_SET() in Hive, keep duplicates?
Try to use COLLECT_LIST(col) after Hive 0.13.0 SELECT hash_id, COLLECT_LIST(num_of_cats) AS aggr_set FROM tablename WHERE blablabla GROUP BY hash_id ;
How can I create a user-defined function in SQLite?
SQLite does not have support for user-defined functions in the way that Oracle or MS SQL Server does. For SQLite, you must create a callback function in C/C++ and hook the function up using the sqlite3_create_function call. Unfortunately, the SQLite API for Android does not allow for the sqlite3_create_function call directly through Java. In order … Read more
SQL Server: Could not find type in the assembly
Try CREATE FUNCTION NormalizeString(@s nvarchar(max), @normalizationForm nvarchar(50)) RETURNS nvarchar(max) AS EXTERNAL NAME CLRFunctions.[CLRFunctions.T].NormalizeString
How to add description to functions and function parameters?
All you have to do is key three apostrophes on the line before your function. .NET will add the rest of the code for you. Insert the text you want displayed in the intellisense in the tag. ”’ <summary> ”’ Returns the name of the code. ”’ </summary> Function GetName() As String Return “Something” End … Read more
Applying UDFs on GroupedData in PySpark (with functioning python example)
Since Spark 2.3 you can use pandas_udf. GROUPED_MAP takes Callable[[pandas.DataFrame], pandas.DataFrame] or in other words a function which maps from Pandas DataFrame of the same shape as the input, to the output DataFrame. For example if data looks like this: df = spark.createDataFrame( [(“a”, 1, 0), (“a”, -1, 42), (“b”, 3, -1), (“b”, 10, -2)], … Read more
Derive multiple columns from a single column in a Spark DataFrame
Generally speaking what you want is not directly possible. UDF can return only a single column at the time. There are two different ways you can overcome this limitation: Return a column of complex type. The most general solution is a StructType but you can consider ArrayType or MapType as well. import org.apache.spark.sql.functions.udf val df … Read more