SQL Server 查询以查找 CHI-SQUARE 值(不工作) [英] SQL Server Query to find CHI-SQUARE Values (Not Working)

问题描述

我试图从我对示例数据的以下 SQL Server 查询中找到卡方检验:

I am trying to find the Chi-Square test from my following SQL Server Query on the sample data:

 SELECT sessionnumber, sessioncount, timespent, expected, dev, dev*dev/expected as    chi_square
 FROM (SELECT clusters.sessionnumber, clusters.sessioncount, clusters.timespent,
 (dim1.cnt * dim2.cnt * dim3.cnt)/(dimall.cnt*dimall.cnt) as expected,
 clusters.cnt-(dim1.cnt * dim2.cnt * dim3.cnt)/(dimall.cnt*dimall.cnt) as dev
 FROM clusters JOIN
 (SELECT sessionnumber, SUM(cnt) as cnt FROM clusters
 GROUP BY sessionnumber) dim1 ON clusters.sessionnumber = dim1.sessionnumber JOIN
 (SELECT sessioncount, SUM(cnt) as cnt FROM clusters
 GROUP BY sessioncount) dim2 ON clusters.sessioncount = dim2.sessioncount JOIN
 (SELECT timespent, SUM(cnt) as cnt FROM clusters
 GROUP BY timespent) dim3 ON clusters.timespent = dim3.timespent CROSS JOIN
 (SELECT SUM(cnt) as cnt FROM clusters) dimall) a

我的表有这种样本数据:

My table has this sort of sample data:

sessionnumber   sessioncount    timespent       cnt
1                  17               28          NULL
2                  22               8           NULL
3                  1                1           NULL
4                  1                1           NULL
5                  8               111          NULL
6                  8                65          NULL
7                  11               5           NULL
8                  1                1           NULL
9                  62               64          NULL
10                 6                42          NULL

问题是这个查询工作正常，但它给出了错误的输出，或者你可以说根本没有输出.它给我的输出是这样的:

The problem is that this query works fine but it gives wrong output or you can say no output at all. The output it gives my is like:

sessionnumber   sessioncount    timespent       expected    dev     chi_square
1               17              28              NULL        NULL    NUL
2               22              8               NULL        NULL    NULL
3               1               1               NULL        NULL    NULL
4               1               1               NULL        NULL    NULL
5               8               111             NULL        NULL    NULL
6               8               65              NULL        NULL    NULL
7               11              5               NULL        NULL    NULL
8               1               1               NULL        NULL    NULL
9               62              64              NULL        NULL    NULL
10              6               42              NULL        NULL    NULL

我怎样才能摆脱这个问题，因为我已经尽力了！提前感谢告诉我我做错了什么！

How can I get rid of this problem because I tried my best at all! Thanks in advance telling me what I' doing wrong!

推荐答案

在你的示例数据中，cnt为NULL，所以结果也是NULL.您可以使用 ISNULL 将这些 NULL 值替换为默认值(例如 1，我不知道上下文是什么)，例如

In your sample data, cnt is NULL, so the results are also NULL. You can replace these NULL values with a default value (1 for example, I don't know what is the context) using ISNULL, like

SELECT sessionnumber, SUM(ISNULL(cnt, 1)) as cnt FROM clusters GROUP BY sessionnumber

这篇关于SQL Server 查询以查找 CHI-SQUARE 值(不工作)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！