计数列中数值的位置，使用SQL或DAX。

huangapple
go评论111阅读模式
英文:

SQL or DAX Count values in column based on their position

问题

"may I ask you for help?"
"How to count values based on their position?"

  1. 可以请您帮忙吗？
  2. 如何根据位置来计算值？

"I need to have result like this:"

  1. 我需要结果如下：

"x 3"
"y 2"
"x 2"
"y 1"

"Thank you!!!"

  1. 谢谢！！！

"I am using this SQL script but it does not work..."

  1. 我正在使用这个SQL脚本，但它不起作用...
英文:

may I ask you for help?
How to count values based on their position?

  1. column1
  2. x
  3. x
  4. x
  5. y
  6. y
  7. x
  8. x
  9. y

I need to have result like this:

  1. x   3
  2. y   2
  3. x   2
  4. y   1

Thank you!!!

I am using this SQL script but it does not work...

  1. with test as (
  2. 	select *,row_number() over (order by (select null)) as countt
  3. 	FROM [vf_test_v02] as t
  4. )
  5. select column1,
  6. 	       ROW_NUMBER() OVER (PARTITION BY column1 ORDER BY (select null)) as seqnum
  7. from test 
  8. group by column1,countt
  9. ORDER BY countt

答案1

得分: 1

你应该始终提供排序顺序，否则会浪费宝贵的时间。

你可以在SQL中按照以下方式操作：

  1. CREATE TABLE vf_test_v02
  2.     ("column1" varchar(1))
  3. ;
  4.     
  5. INSERT INTO vf_test_v02
  6.     ("column1")
  7. VALUES
  8.     ('x'),
  9.     ('x'),
  10.     ('x'),
  11.     ('y'),
  12.     ('y'),
  13.     ('x'),
  14.     ('x'),
  15.     ('y')
  16. ;
  1. 8 行受影响
  1. with test as (
  2.     select column1, row_number() over (order by (select null)) as rn
  3.     FROM [vf_test_v02] as t
  4. ), LG as (SELECT
  5.    rn, column1, CASE WHEN column1 <> LAG("column1") OVER(order by rn) THEN 1 ELSE 0 END lg
  6. FROM test), SU as (
  7. SELECT 
  8.    column1, SUM(lg) OVER(order by rn) su
  9. FROM LG)
  10. SELECT
  11.    column1, COUNT(*) countt
  12.   FROM SU
  13.   GROUP BY column1, su
英文:

You should always pprovide a sorting order, else it costs precious time.

You can do in SQL following

  1. CREATE TABLE vf_test_v02
  2.     (&quot;column1&quot; varchar(1))
  3. ;
  4.     
  5. INSERT INTO vf_test_v02
  6.     (&quot;column1&quot;)
  7. VALUES
  8.     (&#39;x&#39;),
  9.     (&#39;x&#39;),
  10.     (&#39;x&#39;),
  11.     (&#39;y&#39;),
  12.     (&#39;y&#39;),
  13.     (&#39;x&#39;),
  14.     (&#39;x&#39;),
  15.     (&#39;y&#39;)
  16. ;
  1. 8 rows affected
  1. with test as (
  2.     select column1,row_number() over (order by (select null)) as rn
  3.     FROM [vf_test_v02] as t
  4. ), LG as (SELECT
  5.    rn,column1, CASE WHEN column1 &lt;&gt; LAG(&quot;column1&quot;) OVER(order by rn) THEN 1 ELSe 0 END lg
  6. FROM test), SU as (
  7. SELECT 
  8.    column1, SUM(lg) OVER(order by rn) su
  9. FROM LG)
  10. SELECT
  11.    column1, COUNT(*) countt
  12.   FROM SU
  13.   GROUP BY column1,su
column1 countt
x 3
y 2
x 2
y 1

fiddle

huangapple
  • 本文由 发表于 2023年7月31日 22:49:19
  • 转载请务必保留本文链接：https://go.coder-hub.com/76804770.html
  • count
  • dax
  • sql
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定