Microsoft Fabric workout - Generate numbers

Power Query で記述してみたらってこともならべておく。
# range
- データ型: [pyspark.sql.types.LongType](https://spark.apache.org/docs/3.4.1/api/python/reference/pyspark.sql/api/pyspark.sql.types.LongType.html) / LONG, BIGINT
- 列名: `id`
- `end` を含まない

example: start `0` end `10` step `2`

https://spark.apache.org/docs/3.4.1/api/python/reference/pyspark.sql/api/pyspark.sql.SparkSession.range.html
```python:PySpark : Runtime 1.2
spark.range(0,10,2).show()
```
https://spark.apache.org/docs/3.4.1/sql-ref-syntax-qry-select-tvf.html
```sql:Spark SQL : Runtime 1.2
SELECT
    *
FROM
    range(0, 10, 2)
```

https://spark.apache.org/docs/3.4.1/api/python/reference/pyspark.sql/api/pyspark.sql.functions.sequence.html

```text:Power Query : range 相当
Table.FromColumns(
    {
        List.Generate(
            ()=> 0,
            each _ < 10,
            each _ + 2
        )
    },
    type table [id = Int64.Type]
)
```

# sequence
https://spark.apache.org/docs/3.4.1/api/sql/#sequence
> Generates an array of elements from start to stop (inclusive), incrementing by step. The type of the returned elements is the same as the type of argument expressions.
Supported types are: byte, short, integer, long, date, timestamp.
- `stop` も含む

## number
```python:PySpark : Runtime 1.2
from pyspark.sql.functions import lit, sequence, explode

(spark.range(1)
    .select(
        explode(
            sequence(lit(1), lit(5))
        )
    )
).show()
```

```sql:Spark SQL : Runtime 1.2
SELECT
    explode(
        sequence(1, 5)
    )
```

```text:Power Query
Table.ExpandListColumn(
    #table(
        type table [col = {number}],
        {
            {
                {1..5}
            }
        }
    ),
    "col"
)
```
## date
```python:PySpark : Runtime 1.2
from pyspark.sql.functions import lit, sequence, explode, to_date, expr

(spark.range(1)
    .select(
        explode(
            sequence(
                to_date(lit("2024-04-01"))
                , to_date(lit("2025-03-31"))
                , expr("interval 1 day")
            )
        ).alias("column_date")
    )
).show()
```

```sql:Spark SQL : Runtime 1.2
SELECT
    explode(
        sequence(
            to_date("2024-04-01")
            , to_date("2025-03-31")
            , interval 1 day
        )
    ) AS column_date
```

```text:Power Query
Table.FromColumns(
    {
        List.Generate(
            ()=> Date.From("2024-04-01"),
            each _ <= Date.From("2025-03-31"),
            each _ + #duration(1, 0, 0, 0)
            // each Date.AddDays(_, 1)
        )
    },
    type table [column_date = date]
)
```

## datetime
```python:PySpark : Runtime 1.2
from pyspark.sql.functions import lit, sequence, explode, to_timestamp, expr

(spark.range(1)
    .select(
        explode(
            sequence(
                to_timestamp(lit("2024-04-01 00:00:00"))
                , to_timestamp(lit("2024-04-01 23:59:59"))
                , expr("INTERVAL '1:30' HOUR TO MINUTE")
            )
        ).alias("column_datetime")
    )
).show()
```

```sql:Spark SQL : Runtime 1.2
SELECT
    explode(
        sequence(
            to_timestamp("2024-04-01 00:00:00")
            , to_timestamp("2024-04-01 23:59:59")
            , INTERVAL '1:30' HOUR TO MINUTE
        )
    ) AS column_datetime
```

# 思ったこと🙄
TIMESTAMP の列では タイムゾーン情報は切り捨てて、UTC として。
TIMESTAMP_NTZ の列は Lakehouse - SQL Analytics endpoint でサポートされない。

# その他
https://powerbi.connpass.com/

https://www.microsoft.com/en-us/power-platform/products/power-bi

https://www.microsoft.com/en-us/microsoft-fabric