ClickHouse / ClickHouse/ClickHouse

kurtSamp & skewSamp return non commonly expected result (scipy)

Open
#51,995 1 comment 1 reaction 0 assignees View on GitHub
comp-aggregate-functions external unexpected behaviour
Dominant language
C++
Stars
49.9k
Forks
9k
Avg merge
21h 32m
Merged PRs (30d)
515

Description

**Describe the unexpected behaviour**

```
dataset = [10, 17, 71, 6, 55, 38, 27, 61, 48, 46, 21, 38, 2, 67, 35, 77, 29, 31, 27, 67, 81, 82, 75, 81, 31, 38, 68, 95, 37, 34, 65, 59, 81, 28, 82, 80, 35, 3, 97, 42, 66, 28, 85, 98, 45, 15, 41, 61, 24, 53, 97, 86, 5, 65, 84, 18, 9, 32, 46, 52, 69, 44, 78, 98, 61, 64, 26, 11, 3, 19, 0, 90, 28, 72, 47, 8, 0, 74, 38, 63, 88, 43, 81, 61, 34, 24, 37, 53, 79, 72, 5, 77, 58, 3, 61, 56, 1, 3, 5, 61]
```

## Skew

```python
print(skew(dataset, bias=False))

# -0.05873838908626328
```
```sql
WITH arrayJoin(dataset) AS value
SELECT
skewSamp(value) AS ex_1,
(pow(count(), 2) * ex_1) / ((count() - 1) * (count() - 2)) AS G

┌─────────────────ex_1─┬────────────────────G─┐
│ -0.05698798509149213 │ -0.05873838908626276 │
└──────────────────────┴──────────────────────┘
```

## Kurt(osis)

```python
print(kurtosis(dataset, bias=False))

# -1.0924286152713967
```
```sql
WITH arrayJoin(dataset) AS value
SELECT
kurtSamp(value) AS ex_1,
(((pow(count(), 2) * (count() + 1)) / (((count() - 1) * (count() - 2)) * (count() - 3))) * ex_1) - ((3 * pow(count() - 1, 2)) / ((count() - 2) * (count() - 3))) AS G

┌──────────────ex_1─┬───────────────────G─┐
│ 1.864177212613638 │ -1.0924286152714027 │
└───────────────────┴─────────────────────┘
```

**Expected behavior**
Result will match commonly used tools without weird math tricks.

Add compatibility parameters for functions, which will return expected result

```
SELECT
kurtSamp(legacy)(value) AS ex_1,
kurtSamp(value) AS G

┌──────────────ex_1─┬───────────────────G─┐
│ 1.864177212613638 │ -1.0924286152714027 │
└───────────────────┴─────────────────────┘
```

**Additional context**
Full example
Google Collab https://colab.research.google.com/drive/1xoWNi7QAJ9XZtCbmQqJFB8Z_mCreITPW?usp=sharing
https://github.com/ClickHouse/ClickHouse/issues/51275

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.